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 |
