Combine multi rows and columns in SQL Server

Question

I have a table table1 like this:

sl next   day count status
--------------------------
A    1     1    1    Yes
A    1     1    2    Yes
A    1     1    3    Yes
A    1     1    4    Yes

A    1     2    1    Yes
A    1     2    2    No
A    1     2    3    Yes
A    1     2    4    Yes

A    2     1    1    Yes
A    2     1    2    Yes
A    2     1    3    Yes
A    2     1    4    Yes

and output should be like:

sl  next    status  daylist
------------------------------
A    1      Yes     1-All
                    2-1,3,4

A    1      No      2-2 

A    2      Yes     1-All

As you seen the output, daylist is the combination of count and day which are grouped by status per next level.

Can anyone help me achieve this in T-SQL?

Thanks in advance!

Answer 1

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           Yes    2-1,3,4
A    1           No     2-2
A    2           Yes    1-All

Answer 2

Declare @YourTable table (sl varchar(10),next int,day int,count int, status varchar(10))
Insert Into @YourTable values
('A' ,1 ,1 ,1 ,'Yes'),
('A' ,1 ,1 ,2 ,'Yes'),
('A' ,1 ,1 ,3 ,'Yes'),
('A' ,1 ,1 ,4 ,'Yes'),
('A' ,1 ,2 ,1 ,'Yes'),
('A' ,1 ,2 ,2 ,'No'),
('A' ,1 ,2 ,3 ,'Yes'),
('A' ,1 ,2 ,4 ,'Yes'),
('A' ,2 ,1 ,1 ,'Yes'),
('A' ,2 ,1 ,2 ,'Yes'),
('A' ,2 ,1 ,3 ,'Yes'),
('A' ,2 ,1 ,4 ,'Yes')


Select sl      = IIF(Lag(concat(sl,next,status),1) over (Order by RN)=concat(sl,next,status),'',sl)
      ,next    = IIF(Lag(concat(sl,next,status),1) over (Order by RN)=concat(sl,next,status),'',cast(next as varchar(25)))
      ,status  = IIF(Lag(status,1) over (Order by RN)=status,'',status)
      ,daylist = concat(day,'-',iif(rowCnt=maxRow,'All',string))
 From (
        Select *,RN = Row_Number() over (Order by sl,next,day,status desc)
         From  (
                Select sl,Next,Status,Day,rowCnt=count(*)
                 From  @YourTable 
                 Group By sl,Next,Status,Day 
                ) A
         Cross Apply (Select maxRow=count(*) From @YourTable Where sl=A.sl and next=A.next and day=A.day ) C
         Cross Apply (
                       Select String = Stuff((Select ',' +cast(count as varchar(25)) 
                                               From @YourTable 
                                               Where sl=A.sl and next=A.next and day=A.day and status=A.status
                                               For XML Path ('')),1,1,'') 
                     ) B
     ) A
Order By RN

Returns

sl  next    status  daylist
A   1       Yes     1-All
                    2-1,3,4
A   1       No      2-2
A   2       Yes     1-All

Answer 3

This sort of thing should really be done in the application layer instead of with sql.

A two step concatenation using the stuff() with select ... for xml path ('') method of string concatenation and a common table expression:

with cte as (
  select
      sl
    , next
    , status
    , day
    , daylist = stuff((
        select 
            ','+convert(varchar(10),i.count)
        from t as i
        where t.status = i.status
          and t.day = i.day
          and t.next = i.next
        order by i.count
        for xml path (''), type).value('.','varchar(max)')
        ,1,1,'')      
  from t
  group by sl, next, status, day
)
select
    sl
  , next
  , status
  , daylist = stuff((
      select 
          char(10)+convert(varchar(10),day)+'-'+i.daylist
      from cte as i
      where cte.status = i.status
        and cte.next = i.next
      order by i.day
      for xml path (''), type).value('.','varchar(max)')
      ,1,1,'')      
from cte
group by sl, next, status
order by next, status desc

rextester demo: http://rextester.com/FQE41002

returns:

+----+------+--------+-----------+
| sl | next | status |  daylist  |
+----+------+--------+-----------+
| A  |    1 | Yes    | 1-1,2,3,4 |
|    |      |        | 2-1,3,4   |
| A  |    1 | No     | 2-2       |
| A  |    2 | Yes    | 1-1,2,3,4 |
+----+------+--------+-----------+