Soham Shah Soham Shah - 21 days ago 7
SQL Question

Comma Separated Value in Select Clause in SQL

I have two tables: CONFIRMATION & CONFIRMATION_PRESS

CONFORMATION Table looks like following:

ID_CONF | ID_LOT | QTY
1005 175 25
1006 175 24
1007 175 23
1008 176 50


CONFIRMATION_PRESS Table looks like following:

ID_CONF | ID_PRESS
1005 11
1005 22
1005 33
1006 12
1006 13
1007 14


Now I want all the data for ID_LOT = 175 from CONFIRMATION Table but I need ID_PRESS in comma separated value.

For example, for ID_LOT 175, the following results should be displayed:

ID CONF | ID_LOT | QTY | ID_PRESS
--------+--------+-----+----------
1005 175 25 11,22,33
1006 175 24 12,13
1007 175 23 14


My query looks like this:

SELECT
C.ID_CONF,
C.QTY,
C.ID_LOT,
STUFF((
SELECT CAST(',' AS VARCHAR(MAX)) + CAST(CP.ID_PRESS AS VARCHAR(5))
FROM CONFIRMATION C,
CONFIRMATION_PRESS CP
WHERE
C.ID_CONF = CP.ID_CONF
FOR XML PATH('')), 1, 1, '') PRESS_CSV
FROM
CONFIRMATION C
WHERE
C.ID_LOT = 175


But it returns this output:

ID CONF | ID_LOT | QTY | ID_PRESS
--------+--------+-----+------------------
1005 175 25 11,22,33,12,13
1006 175 24 11,22,33,12,13
1007 175 23 11,22,33,12,13


What am I doing wrong here?

Kindly help !

Regards !

Answer

Don't join again the CONFIRMATION table on the subquery. You want just the rows related to your external query.

SELECT
   C.ID_CONF,
   C.QTY,
   C.ID_LOT,
   STUFF((  
      SELECT CAST(',' AS VARCHAR(MAX)) + CAST(CP.ID_PRESS AS VARCHAR(5))
      FROM CONFIRMATION_PRESS CP
      WHERE CP.ID_CONF = C.ID_CONF
      FOR XML PATH('')), 1, 1, '') PRESS_CSV    
FROM CONFIRMATION C 
WHERE C.ID_LOT = 175