T-SQL - string concatenation

Question

Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!)

I have two tables on SQL Server 2005 TABLE 1 and TABLE2:

TABLE1

COL1         COL2
1            10
2            20
3            30
4            10
4            20
5            20
6            30
7            10
7            20

TABLE2

COL1         COL2
10            A
20            B
30            C

COL2 in TABLE2 is a character representation of the numerical data in COL2 TABLE1. I hope this is understandable?

I have worked out how to select COL1 and COL2 from TABLE1 and concatenate the results to show this:

COL1         COL2Concat
1            10
2            20
3            30
4            10, 20
5            20
6            30
7            10, 20, 30

Using this:

SELECT  COL1,
        STUFF(( SELECT  ',' + CAST(a.COL2 AS VARCHAR(255)) AS [text()]
                FROM    TABLE1 a
                WHERE   a.COL1 = b.COL1
                ORDER BY a.COL2
              FOR
                XML PATH('')
              ), 1, 1, '') AS COL2Concat
FROM    TABLE1 b
GROUP BY COL1
ORDER BY COL1

But now I'd like to try and get the same result except use the data in COL2 TABLE2... i.e.:

COL1         COL2Concat
1            A
2            B
3            C
4            A, B
5            B
6            C
7            A, B, C

Any ideas - I'm stuck to be honest as I have tried modifying the STUFF query, but it never seems to come out right...

Presumably you've tried just changing all the TABLE1 in your query to TABLE2. I can't see offhand why that doesn't work. What do you get? — Brian Hooper
– Brian Hooper, Commented Jul 30, 2010 at 15:52
Yes exactly.. the examples are simplistic, but I need to show A, B, C concatenated like 10, 20, 30 have been. — JamesH
– JamesH, Commented Jul 30, 2010 at 15:53

Brian Hooper · Accepted Answer · 2010-07-30 16:06:58Z

6

you could try...

SELECT  COL1,
        STUFF(( SELECT  ',' + CAST((SELECT COL2
                                        FROM TABLE2
                                        WHERE TABLE2.COL1 = a.COL1) AS VARCHAR(255)) AS [text()]
                FROM    TABLE1 a
                WHERE   a.COL1 = b.COL1
                ORDER BY a.COL2
              FOR
                XML PATH('')
              ), 1, 1, '') AS COL2Concat
FROM    TABLE1 b
GROUP BY COL1
ORDER BY COL1

answered Jul 30, 2010 at 16:06

Brian Hooper

22.2k25 gold badges91 silver badges146 bronze badges

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

3 Comments

JamesH Over a year ago

Brian - thank you.. you've solved in about 1 minute what has taken me all day to faff about with! Many, many thanks.

Brian Hooper Over a year ago

My pleasure. You'd done all the tough stuff, and overlooked the last little bit.

JamesH Over a year ago

It was knowing that you could put a sub clause in the CAST part... thanks again.

Collectives™ on Stack Overflow

T-SQL - string concatenation

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related