add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 休息