SQL row values to one column

Question

I have a SQL query which return the below data;

I need to write Values to one row depend ID and Code column like below;

What did you already try? Please share your code. What concrete issues are you facing? Thanks for considering [How do I ask a good question](https://stackoverflow.com/help/how-to-ask)? and [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). — Christian Baumann, Oct 14 '20 at 06:14
[COALESCE](https://stackoverflow.com/questions/8700961/using-coalesce-function-to-make-values-separated-with-commas) might help — Singh, Oct 14 '20 at 06:17

score 0 · Accepted Answer · answered Oct 14 '20 at 06:14

0

SELECT ID, Code, STRING_AGG(Value) AS Value
FROM dbo.Table
GROUP BY ID, Code;

Considering you're using up to date version.

answered Oct 14 '20 at 06:14

Evaldas Buinauskas

13,739
11
55
107

Thats solve it i think. First attempt is succesfull. Thank you. – Lacrymae Oct 14 '20 at 06:23
If this all works - please accept this answer! :) – Evaldas Buinauskas Oct 14 '20 at 06:36

score 0 · Answer 2 · answered Oct 14 '20 at 06:18

Please check below attempt. There are other options also you can use.

 SELECT
    CAST(408 AS INT) AS ID,
    CAST(1 AS INT) AS CODE,
    CAST('A' AS VARCHAR(20)) AS VALUE
    INTO 
    #tmpgroupby
    
    INSERT INTO #tmpgroupby
    VALUES 
    (408,1,'B'),
    (408,1,'C'),
    (408,1,'D'),
    (408,1,'E'),
    (408,1,'F'),
    (408,1,'G'),
    (408,2,'H'),
    (408,2,'I'),
    (408,2,'J'),
    (408,2,'K')
    
    SELECT ID,CODE, STUFF(
             (SELECT ', ' + convert(varchar(10), t2.VALUE, 120)
              FROM #tmpgroupby t2
              where t1.ID = t2.ID
              AND t1.code = t2.CODE
              FOR XML PATH (''))
              , 1, 1, '') 
               FROM #tmpgroupby t1
    GROUP BY ID,CODE

--- below also give the same result
  
SELECT ID, Code, STRING_AGG(VALUE,',') AS Value
FROM dbo.#tmpgroupby
GROUP BY ID, Code;

    DROP TABLE #tmpgroupby

score 0 · Answer 3 · answered Oct 14 '20 at 06:45

0

For Sql Server:

SELECT ID, CODE,
 Value=STUFF((SELECT distinct ',' + Value FROM table_name t1 
             WHERE t.Code =  t1.Code FOR XML PATH ('')), 1, 1, '') 

 FROM table_name AS t
 GROUP BY ID, Code

answered Oct 14 '20 at 06:45

Rasel Ahmed

86
4

SQL row values to one column

3 Answers3