Comma Separated Value in Select Clause in SQL

Question

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 !

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Nov 18, 2016 at 11:25
I wrote this query just for example. I usually use INNER JOIN / OUTER JOIN in my queries. Thanks for the feedback though ! — Soham Shah
– Soham Shah, Commented Nov 18, 2016 at 11:49

Marc Guillot · Accepted Answer · 2016-11-19 03:42:44Z

1

Don't join again the CONFIRMATION table on the subquery. You want the rows related to your main 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

edited Nov 19, 2016 at 3:42

answered Nov 18, 2016 at 11:24

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soham Shah Over a year ago

Worked like a charm. Silly me !! Thanks a ton :)

Alfaiz Ahmed · Accepted Answer · 2016-11-18 11:48:30Z

Begin Tran
Create Table CONFIRMATION  (ID_CONF Int,ID_LOT Int,QTY numeric)
INSert into CONFIRMATION
Select 1005,175,25 Union All
Select 1006,175,24  Union All
Select 1007,175,23 Union All
Select 1008,176,50

Create Table CONFIRMATION_PRESS (ID_CONF int ,ID_PRESS INt )
Insert into CONFIRMATION_PRESS
Select 1005,11 Union All
Select 1005,22 Union All
Select 1005,33 Union All
Select 1006,12 Union All
Select 1006,13 Union All
Select 1007,14

--Select *from CONFIRMATION Inner Join
--CONFIRMATION_PRESS On  CONFIRMATION_PRESS.ID_CONF=CONFIRMATION.ID_CONF
--Where CONFIRMATION.ID_LOT=175


SELECT
    C.ID_CONF,
    C.QTY,
    C.ID_LOT,
    STUFF((  
            SELECT  ', ' +  CONVERT(Nvarchar,CP.ID_PRESS)
            from  
            CONFIRMATION_PRESS CP

      WHERE
          C.ID_CONF = CP.ID_CONF
      FOR XML PATH('')), 1, 2, '') PRESS_CSV
FROM
    CONFIRMATION C 
WHERE 
    C.ID_LOT = 175

RollBack Tran

Collectives™ on Stack Overflow

Comma Separated Value in Select Clause in SQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related