Need to concatenate column values of same row

Question

After so few Joins my View is like this

ID      Code

1       US
1       UK
1       GR
2       US
2       IN
3       US  
3       AF
3       RU
3       SA

Required Result :

ID  Code

1   US,UK,GR
2   US,IN
3   US,AF,RU,SA

I tried using FOR XML PATH.

But as the number of records are high in my table it is causing performance issue it is taking more than 20 minutes to execute

[Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) — Maciej Los, Jul 01 '16 at 05:34
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Liesel, Jul 01 '16 at 05:38
Very informative I have to try the one that improves performance. — blackbookstar, Jul 01 '16 at 05:38
I tried XML Path, but execution time is the issue. T resolve this select into tableN from View and on that table I have applied XML path. But my table updates daily, to update the tableN again I have to create SQL job. which I wants to consider the last option. If there is any better way would be helpful — blackbookstar, Jul 01 '16 at 05:42

score 0 · Answer 1 · answered Jul 01 '16 at 05:46

0

Check the link below, Here you find different examples for your requirement:

Concatenating Row Values in Transact-SQL

answered Jul 01 '16 at 05:46

Rahul Hendawe

902
1
14
39

1

I'd say: someone stole link from my comment. – Maciej Los Jul 01 '16 at 06:04
No @MaciejLos I didn't stolen the link, I actually not seen your comment before posting answer :) – Rahul Hendawe Jul 01 '16 at 06:05
@MaciejLos yes you are right I have seen your comment after your prompt. before 10 min I didn't noticed it :) – Rahul Hendawe Jul 01 '16 at 06:14

score 0 · Accepted Answer · answered Jul 01 '16 at 06:05

0

You can use the below query,

Select T.Id,
       Left(T.[Codes],Len(T.[Codes])-1) As Codes
From
    (
        Select distinct T2.ID, 
            (
                Select T1.Code + ',' AS [text()]
                From @T T1
                Where T1.ID = T2.ID
                ORDER BY T1.ID
                For XML PATH ('')
            ) [Codes]
        From @T T2
    ) T

@T is your input table as in, Declare @T as Table (ID INT, Code varchar(10))

answered Jul 01 '16 at 06:05

SharK

2,155
1
20
28

Assuming that you have added all necessary constraints to the table!! – SharK Jul 07 '16 at 04:57
I have split the column values into columns. This improved performance. For XML Path works but in my case performance is reduced if I use it – blackbookstar Jul 09 '16 at 04:49

Need to concatenate column values of same row

2 Answers2