Comma-separated columns in SQL Server

Question

I have a problem: I have two tables

Table1 which has two columns

Col1   Col2 
----  ------
a     value1
b     value1
b     value1

And Table2

Col1   Col2 
----  ------
1     a,b
2     a,c
3     a,b,c

I want result

Col1   Col2
-----  -----
a      1,2,3
b      1,3
c      2,3

Hm it is not possible, because you don't have field to join tables. You have to have some kind of key to join them — bksi
– bksi, Commented Sep 25, 2013 at 5:34
How is table 1 relevant to the question? It looks like all of the data in your desired result is contained within table 2 — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 25, 2013 at 6:04

Mikael Eriksson · Accepted Answer · 2013-09-25 06:34:19Z

1

WITH C AS
(
  SELECT T2.Col1,
         S.Item
  FROM Table2 AS T2
    CROSS APPLY dbo.SplitStrings(T2.Col2, ',') AS S
)
SELECT C1.Item AS Col1,
       (
       SELECT ','+CAST(C2.Col1 AS VARCHAR(10))
       FROM C AS C2
       WHERE C1.Item = C2.Item
       ORDER BY C2.Col1
       FOR XML PATH(''), TYPE
       ).value('substring(text()[1], 2)', 'VARCHAR(MAX)') AS Col2
FROM C AS C1
GROUP BY C1.Item

SQL Fiddle

edited Sep 25, 2013 at 6:34

answered Sep 25, 2013 at 6:16

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

coder · Accepted Answer · 2013-09-25 05:39:47Z

0

Try this:

NOTE: Not tested

select col1, [col2],
(select col1+',' from Table2 where Col2=ID
group by col1 for xml path('')) AS Col2
From Table1

answered Sep 25, 2013 at 5:39

coder

13.2k32 gold badges116 silver badges221 bronze badges

Collectives™ on Stack Overflow

Comma-separated columns in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related