SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE XTable 
	( [order] varchar(20),[sn] varchar(7),[ag] varchar(7) , [af] varchar(7));
INSERT INTO XTable
	([order],[sn],[ag],[af])
VALUES
('a','0001','123','0'),
('a','0001','124','0'),
('a','0001','125','0'),
('a','0001','126','0'),
('a','0003','123','1'),
('a','0003','123','2'),
('a','0003','124','0'),
('a','0003','125','0'),
('a','0003','126','0'),
('b','0005','123','0'),
('b','0005','124','0'),
-- 多加入 b ,0005,126,0
('b','0005','126','0'),
--
('b','0006','123','0'),
('b','0006','124','1'),
('b','0006','124','2'),
('b','0006','125','0');
16 rows affected

SELECT *
FROM XTable


order
sn
ag
af


a
0001
123
0

a
0001
124
0

a
0001
125
0

a
0001
126
0

a
0003
123
1

a
0003
123
2

a
0003
124
0

a
0003
125
0

a
0003
126
0

b
0005
123
0

b
0005
124
0

b
0005
126
0

b
0006
123
0

b
0006
124
1

b
0006
124
2

b
0006
125
0


--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM (SELECT [order],[sn],[ag],[af] FROM  XTable) as MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--


order
sn
ag_123
ag_124
ag_125
ag_126


a
0001
0
0
0
0

a
0003
2
0
0
0

b
0005
0
0
null
0

b
0006
0
2
0
null


--[ag_123],[ag_124],[ag_125],[ag_126] 4 個皆為 '0' , ff=1
SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN [ag_123]+[ag_124]+[ag_125]+[ag_126]='0000' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]


order
bb
ff


a
1
1

b
1
0


-- [ag_126]='0' 且 其它 3 個為 '0' 或 null , ff=1
SELECT [order],
SUM(CASE WHEN [ag_123]<>'0' OR [ag_124]<>'0'
OR [ag_125]<>'0' OR [ag_126]<>'0' THEN 1 ELSE 0 END) AS bb,
SUM(CASE WHEN (([ag_123]='0' or [ag_123] is null) 
and ([ag_124]='0' or [ag_124] is null)
and ([ag_125]='0' or [ag_125] is null)) and [ag_126]='0' THEN 1 ELSE 0 END) AS ff
FROM (
--行轉列
SELECT [order],[sn],[123] AS 'ag_123',[124] AS 'ag_124',
[125] AS 'ag_125',[126] AS 'ag_126'
FROM  (SELECT [order],[sn],[ag],[af] FROM  XTable) AS MTable
PIVOT (MAX([af]) FOR [ag] IN ([123],[124],[125],[126])) PTable
--
) AS YTable
WHERE 1=1
GROUP BY [order]
ORDER BY [order]


order
bb
ff


a
1
1

b
1
1
order	sn	ag	af
a	0001	123	0
a	0001	124	0
a	0001	125	0
a	0001	126	0
a	0003	123	1
a	0003	123	2
a	0003	124	0
a	0003	125	0
a	0003	126	0
b	0005	123	0
b	0005	124	0
b	0005	126	0
b	0006	123	0
b	0006	124	1
b	0006	124	2
b	0006	125	0