DECLARE @tb TABLE(sl VARCHAR(2),[next] INT,[day] INT ,[count] INT,status VARCHAR(3))
INSERT INTO @tb
SELECT 'A',1,1,1,'Yes' UNION ALL
SELECT 'A',1,1,2,'Yes' UNION ALL
SELECT 'A',1,1,3,'Yes' UNION ALL
SELECT 'A',1,1,4,'Yes' UNION ALL
SELECT 'A',1,2,1,'Yes' UNION ALL
SELECT 'A',1,2,2,'No' UNION ALL
SELECT 'A',1,2,3,'Yes' UNION ALL
SELECT 'A',1,2,4,'Yes' UNION ALL
SELECT 'A',2,1,1,'Yes' UNION ALL
SELECT 'A',2,1,2,'Yes' UNION ALL
SELECT 'A',2,1,3,'Yes' UNION ALL
SELECT 'A',2,1,4,'Yes'
SELECT sl,[next],[status] ,LTRIM(t.[day])+'-'+STUFF(c.s,1,1,'') AS daylist
FROM @tb AS t
CROSS APPLY(
SELECT CASE WHEN a.OtherStatusCnt>0 THEN
CASE WHEN a.[status]=t.[status] THEN ','+LTRIM(a.[count]) ELSE '' END
ELSE
CASE WHEN a.id=1 THEN '.All' ELSE '' END
END
FROM (
SELECT tt.count,tt.[status]
,COUNT(CASE WHEN tt.[status]!=t.[status] THEN 1 ELSE NULL END)OVER() AS OtherStatusCnt
,ROW_NUMBER()OVER(ORDER BY tt.[count]) AS id
FROM @tb AS tt WHERE tt.sl=t.sl AND tt.[next]=t.[next] AND tt.[day]=t.[day]
) AS a
FOR XML PATH('')
) c(s)
GROUP BY sl,[next],[day],[status],c.s
ORDER BY sl,[next],[status] desc
sl next status daylist
---- ----------- ------ ----------
A 1 Yes 1-All
A 1 No Yes 2-21,3,4
A 1 YesNo 2-1,3,42
A 2 Yes 1-All
