Tutorial :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...


Solution:1

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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »