Convert multiple rows with a comma seperator value by performing Group By Operation-CodePudding

I have a table with Contract_Id, Site_Name, and Hours columns… and now my requirement is to generate the comma seperated values by performing Group By on Contract_Id column.

Contract_Id Site_Name   Hours
1           X           20
1           Y           20
2           X           20

Required Output in the Below Format

Contract_Id Site_Name   Hours
1           X,Y         40
2           X           20

I have tried with the below XML Path by doing group by... but it didn't work!!...

SELECT STUFF((SELECT ','   Name,Contract_Id
            FROM #Tbl_Practice
            GROUP BY Name,Contract_Id
            FOR XML PATH('')) ,1,1,'') AS Txt

Can anyone provide me the SQL solution to handle this in a dynamic way….

CodePudding user response：

You need to correlate the string aggregation handled by FOR XML PATH with the source. Here is one way:

SELECT 
  Contract_Id, 
  Site_Name = STUFF((SELECT ','   Site_Name
      FROM #Tbl_Practice 
      WHERE Contract_Id = t.Contract_Id
      FOR XML PATH(''), 
      TYPE).value(N'.[1]', N'varchar(max)'),1,1,''),
  Hours = SUM(Hours)
FROM #Tbl_Practice AS t
GROUP BY Contract_Id;

Working example in this fiddle.

CodePudding user response：

Since you don't have STRING_AGG in 2016, there's another way to go which gives you a little more control over things than the XML_PATH/Stuff blackbox.

DECLARE @table TABLE (Contract_ID INT, Site_Name NVARCHAR(20), Hours INT)
INSERT INTO @table (Contract_ID, Site_Name, Hours) VALUES 
(1, 'X',20),
(1, 'Y',20),
(2, 'X',20);

;WITH base AS (
SELECT Contract_ID, Site_Name, Hours, ROW_NUMBER() OVER (PARTITION BY Contract_ID ORDER BY Site_Name) AS rn
  FROM @table
), rCTE AS (
SELECT Contract_ID, CAST(Site_Name AS NVARCHAR(MAX)) AS Site_Name, Hours, rn
  FROM base
 WHERE rn = 1
UNION ALL
SELECT a.Contract_ID, a.Site_Name ',' r.Site_Name, a.Hours r.Hours, r.rn
  FROM rCTE a
    INNER JOIN base r
      ON a.Contract_ID = r.Contract_ID
      AND a.rn   1 = r.rn
)

SELECT r.Contract_ID, r.Site_Name, r.Hours
  FROM rCTE r
    INNER JOIN (SELECT Contract_ID, MAX(rn) AS rn FROM rCTE GROUP BY rCTE.Contract_ID) a
      ON r.Contract_ID = a.Contract_ID
      AND r.rn = a.rn

By Manipulating the order by in the ROW_NUMBER you can adjust how the items are ordered.

Contract_ID Site_Name   Hours
-----------------------------
1           X,Y         40
2           X           20