CREATE TABLE TEST
(a1 nvarchar(20),a2 nvarchar(20), a3 nvarchar(20),a4 nvarchar(20),a5 nvarchar(20));

INSERT INTO TEST SELECT a1='AAA',a2='20190911',a3='1111-111',a4='10',a5=N'生鏽';
INSERT INTO TEST SELECT a1='AAA',a2='20190911',a3='1111-111',a4='30',a5=N'不良';
INSERT INTO TEST SELECT a1='BBB',a2='20191001',a3='2222-222',a4='50',a5=N'試做';
INSERT INTO TEST SELECT a1='BBB',a2='20191001',a3='2222-222',a4='70',a5=N'休息';
INSERT INTO TEST SELECT a1='AAA',a2='20190911',a3='1111-111',a4='20',a5=N'磨損';
INSERT INTO TEST SELECT a1='BBB',a2='20191001',a3='2222-222',a4='40',a5=N'調機';
INSERT INTO TEST SELECT a1='BBB',a2='20191001',a3='2222-222',a4='60',a5=N'加工';

SELECT a1,a2,a3,
MAX(CASE [a4_code] WHEN 'code1' THEN a4 ELSE '' END) N'code1',
MAX(CASE [a5_reason] WHEN 'reason1' THEN a5 ELSE '' END) N'reason1',	
MAX(CASE [a4_code] WHEN 'code2' THEN a4 ELSE '' END) N'code2',
MAX(CASE [a5_reason] WHEN 'reason2' THEN a5 ELSE '' END) N'reason2',
MAX(CASE [a4_code] WHEN 'code3' THEN a4 ELSE '' END) N'code3',
MAX(CASE [a5_reason] WHEN 'reason3' THEN a5 ELSE '' END) N'reason3',
MAX(CASE [a4_code] WHEN 'code4' THEN a4 ELSE '' END) N'code4',
MAX(CASE [a5_reason] WHEN 'reason4' THEN a5 ELSE '' END) N'reason4'
FROM 
(
select a1,a2,a3,a4,a5,
'code'+convert(varchar,row_number()
over(partition by a1,a2,a3 order by a1,a2,a3,a4)) as a4_code,
'reason'+convert(varchar,row_number()
over(partition by a1,a2,a3 order by a1,a2,a3,a4)) as a5_reason
from TEST
) as N
GROUP BY a1,a2,a3
ORDER BY a1,a2,a3

Demo

如果你想練習SQL,
可以試試看,
要不然我覺得後端語言作起來更容易.

善用GROUP/MAX(CASE WHEN)/ROW_NUMBER() OVER (PARTITION BY )即可.

其實是有對應的語法可以用。
可是一般並不太建議從sql語法來改變呈現方式。

因為結合的筆數少也就算了。
結合的筆數多，就會炸給你看了。

最好還是從其它方式來處理會安全點。