执行以下代码
DECLARE @t TABLE(id INT IDENTITY(1,1), no varchar(20))
INSERT @t select '10,11,12,13,26,28' --- ID01
union all select '04,09,19,20,21,26' --- ID02
union all select '01,07,10,23,28,32' --- ID03
union all select '04,06,07,10,13,25' --- ID04
union all select '04,06,15,17,30,31' --- ID05
union all select '01,03,10,21,26,27' --- ID06
union all select '01,09,19,21,23,26' --- ID07
union all select '05,08,09,14,17,23' --- ID08
union all select '05,09,18,20,22,30' --- ID09
union all select '01,02,08,13,17,24' --- ID10
union all select '04,05,11,12,30,32' --- ID11
union all select '02,12,16,17,27,30' --- ID12
union all select '08,13,17,21,23,32' --- ID13
union all select '03,05,07,08,21,31' --- ID14
union all select '04,11,19,25,26,32' --- ID15
union all select '11,17,28,30,31,33' --- ID16
DECLARE @a TABLE(id INT IDENTITY(1,1), no1 CHAR(2),no2 CHAR(2),no3 CHAR(2),no4 CHAR(2),no5 CHAR(2),no6 CHAR(2))
INSERT @a select '10','11','12','13','26','28' --- ID01
union all select '04','09','19','20','21','26' --- ID02
union all select '01','07','10','23','28','32' --- ID03
union all select '04','06','07','10','13','25' --- ID04
union all select '04','06','15','17','30','31' --- ID05
union all select '01','03','10','21','26','27' --- ID06
union all select '01','09','19','21','23','26' --- ID07
union all select '05','08','09','14','17','23' --- ID08
union all select '05','09','18','20','22','30' --- ID09
union all select '01','02','08','13','17','24' --- ID10
union all select '04','05','11','12','30','32' --- ID11
union all select '02','12','16','17','27','30' --- ID12
union all select '08','13','17','21','23','32' --- ID13
union all select '03','05','07','08','21','31' --- ID14
union all select '04','11','19','25','26','32' --- ID15
union all select '11','17','28','30','31','33' --- ID16
SELECT id1 id,no, [R1]=SUM(CASE WHEN aaa=0 THEN 1 ELSE 0 END ),
[R2]=SUM(CASE WHEN aaa=1 THEN 1 ELSE 0 END),
[R3]=SUM(CASE WHEN aaa=2 THEN 1 ELSE 0 END),
[R4]=SUM(CASE WHEN aaa=3 THEN 1 ELSE 0 END),
[R5]=SUM(CASE WHEN aaa=4 THEN 1 ELSE 0 END),
[R6]=SUM(CASE WHEN aaa=5 THEN 1 ELSE 0 END),
[R7]=SUM(CASE WHEN aaa=6 THEN 1 ELSE 0 END)
FROM
(
SELECT b.*,a.id id1,no,
CASE WHEN CHARINDEX(','+no1+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no2+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no3+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no4+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no5+',',','+no+',')>0 THEN 1 ELSE 0 END
+CASE WHEN CHARINDEX(','+no6+',',','+no+',')>0 THEN 1 ELSE 0 END
aaa
FROM @t a,@a b
)aaa
GROUP BY id1,no
ORDER BY id1
会有这样的结果【简称结果A】
请问如何修改为这样的结果【简称结果B】