SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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'加工';
7 rows affected

select *
from TEST
order by a1,a2,a3,a4


a1
a2
a3
a4
a5


AAA
20190911
1111-111
10
生鏽

AAA
20190911
1111-111
20
磨損

AAA
20190911
1111-111
30
不良

BBB
20191001
2222-222
40
調機

BBB
20191001
2222-222
50
試做

BBB
20191001
2222-222
60
加工

BBB
20191001
2222-222
70
休息


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


a1
a2
a3
a4
a5
a4_code
a5_reason


AAA
20190911
1111-111
10
生鏽
code1
reason1

AAA
20190911
1111-111
20
磨損
code2
reason2

AAA
20190911
1111-111
30
不良
code3
reason3

BBB
20191001
2222-222
40
調機
code1
reason1

BBB
20191001
2222-222
50
試做
code2
reason2

BBB
20191001
2222-222
60
加工
code3
reason3

BBB
20191001
2222-222
70
休息
code4
reason4


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 
(
-- 上面的 SQL
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


a1
a2
a3
code1
reason1
code2
reason2
code3
reason3
code4
reason4


AAA
20190911
1111-111
10
生鏽
20
磨損
30
不良



BBB
20191001
2222-222
40
調機
50
試做
60
加工
70
休息

a1	a2	a3	a4	a5
AAA	20190911	1111-111	10	生鏽
AAA	20190911	1111-111	20	磨損
AAA	20190911	1111-111	30	不良
BBB	20191001	2222-222	40	調機
BBB	20191001	2222-222	50	試做
BBB	20191001	2222-222	60	加工
BBB	20191001	2222-222	70	休息