Grouping by multiple Columns to single as comma separated in SQL Server

smile
 
on Aug 09, 2021 01:30 AM
413 Views

I have a data like that

TagID

T_Date

Diagnosis

Product

Quantity

101

2021-08-01

Fever

A

1

101

2021-08-05

Fever

B

2

101

2021-08-08

Fever

C

3

102

2021-08-03

Fever

AB

2

102

2021-08-07

Fever

BC

3

I want to show output like that

TagID

T_Date [StartDate]

T_Date [LasttDate]

Days

Product

Quantity

101

2021-08-01

2021-08-08

8 Days

A,B,C

1,2,3

102

2021-08-03

2021-08-07

5 Days

AB,BC

2,3

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 09, 2021 06:14 AM

Hi smile,

Refer below query.

SQL

DECLARE @Test AS TABLE(TagID INT,T_Date DATE,Diagnosis VARCHAR(10),Product VARCHAR(10),Quantity INT)
INSERT INTO @Test VALUES(101,'08/01/2021','Fever','A',1)
INSERT INTO @Test VALUES(101,'08/05/2021','Fever','B',2)
INSERT INTO @Test VALUES(101,'08/08/2021','Fever','C',3)
INSERT INTO @Test VALUES(102,'08/03/2021','Fever','AB',2)
INSERT INTO @Test VALUES(102,'08/07/2021','Fever','BC',3)
 
SELECT DISTINCT TagID,
    (SELECT MIN(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID) 'T_Date [StartDate]',
    (SELECT MAX(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID) 'T_Date [LasttDate]',
    CONCAT(DATEDIFF(
        DAY,
        (SELECT MIN(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID),
        (SELECT MAX(T_Date) FROM @Test t1 WHERE t1.TagID = t.TagID)
    ) + 1,' Days') 'Days',
	(STUFF((SELECT ',' + t3.Product
          FROM @Test t3
          WHERE t3.TagID = t.TagID
          FOR XML PATH('')), 1, 1, '')) 'Product',
	(STUFF((SELECT ',' + CAST(t3.Quantity AS VARCHAR(MAX))
          FROM @Test t3
          WHERE t3.TagID = t.TagID
          FOR XML PATH('')), 1, 1, '')) 'Quantity'
FROM @Test t

Output

TagID T_Date [StartDate] T_Date [LasttDate] Days Product Quantity
101 08/01/2021 08/08/2021 8 Days A,B,C 1,2,3
102 08/03/2021 08/07/2021 5 Days AB,BC 2,3