How to combine multiple rows into one ,getting the one column that differs as comma separated values-CodePudding

I want to combine multiple rows into one, getting the one column that differs as comma-separated values.

I have written the below query and it gives the result as shown below. I want 4 rows instead of 9, the last column should appear comma separated like (Storage, Wastewater, Misc).

Please help with your ideas, Thanks in advance!

SELECT DISTINCT
    C.CONTRACTID, C.NUMBER, C.STATE,  
    O.CUSTOMERCODE, O.CUSTOMERNAME,
    C.STARTDATE, C.TERMINATIONDATE, CT.Name AS CONTRACTTYPELIST
FROM  
    [DBO].[CONTRACT] C  
INNER JOIN  
    [ORD].[ORDER] O ON C.CUSTOMERID = O.CUSTOMERID 
INNER JOIN
    [dbo].[Contract_ContractType] CCT ON CCT.ContractId =  C.ContractId
INNER JOIN 
    [Ref].[ContractType] CT ON CT.ContractTypeId = CCT.ContractTypeId
WHERE 
    O.ORDERSTATEID = 6 
ORDER BY 
    c.ContractId

I updated the query like below , but it gives long string in the last column but i want only values for that particular record id in one row. How can this be corrected ?

SELECT distinct 
   C.CONTRACTID,C.NUMBER, C.STATE , 
   O.CUSTOMERCODE,O.CUSTOMERNAME ,
   C.STARTDATE , C.TERMINATIONDATE ,
   STRING_AGG(CAST(CT.Name AS NVARCHAR(MAX)) , ',') AS CONTRACTTYPELIST
FROM 
  [DBO].[CONTRACT] C  
INNER JOIN 
  [ORDERING].[ORDER] O ON C.CUSTOMERID = O.CUSTOMERID 
INNER JOIN
   [dbo].[Contract_ContractType] CCT on CCT.ContractId =  C.ContractId
INNER  JOIN 
   [Ref].[ContractType] CT on CT.ContractTypeId = CCT.ContractTypeId
WHERE 
   O.ORDERSTATEID =  6 
GROUP BY  
C.CONTRACTID,C.NUMBER, C.STATE  , 
O.CUSTOMERCODE,O.CUSTOMERNAME ,
C.STARTDATE , C.TERMINATIONDATE

CodePudding user response：

You want to use a GROUP BY clause together with the STRING_AGG function

Example:

SELECT STRING_AGG(column_D, ',')
FROM dbo.table
GROUP BY column_A, column_B, column_C

CodePudding user response：

Wrap the query with the DISTINCT in a sub-query.
Then use STRING_AGG in the outer query.

SELECT 
  CONTRACTID, [NUMBER], STATE, 
  CUSTOMERCODE, CUSTOMERNAME,
  STARTDATE, TERMINATIONDATE,
  STRING_AGG(CONTRACTTYPE,',') AS CONTRACTTYPELIST
FROM
(
    SELECT DISTINCT
      C.CONTRACTID, C.NUMBER, C.STATE, 
      O.CUSTOMERCODE, O.CUSTOMERNAME,
      C.STARTDATE, C.TERMINATIONDATE,
      CT.Name AS CONTRACTTYPE
    FROM 
      [DBO].[CONTRACT] C  
    JOIN [ORDERING].[ORDER] O 
      ON C.CUSTOMERID = O.CUSTOMERID 
    JOIN [dbo].[Contract_ContractType] CCT 
      ON CCT.ContractId =  C.ContractId
    JOIN [Ref].[ContractType] CT 
      ON CT.ContractTypeId = CCT.ContractTypeId
    WHERE 
       O.ORDERSTATEID =  6 
) q
GROUP BY  
  CONTRACTID, [NUMBER], STATE, 
  CUSTOMERCODE, CUSTOMERNAME,
  STARTDATE, TERMINATIONDATE