question

Vishal Singh avatar image
Vishal Singh asked

Changing row data to comma delimited data

Can anyone help me out in converting below tables data to a defined format as below:

Table Data:

id     country         city
101    India           delhi
101    india           up
101    India           hp
102    US              NY
102    US              RG
103    China           AN
104    Japan           PP

Output

id     country  city
101    India    delhi;up;hp
102    US       NY;RG
103    China    AN
104    Japan    PP
t-sqlquery-resultsformatting
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Vishal - you put "comma delimited" in the subject, but your output is semi-colon delimited... ...unless Kev got a bit carried away with the editing!
1 Like 1 ·
not guilty! although that did thow me at first and I created a csv solution
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

You didn't state a SQL version, but here's one that works in 2005 onwards

declare @mytable table (
id int,
country varchar(10),
city varchar(10))

insert into @mytable
select 101,'India','delhi'
union select 101,'india','up'
union select 101,'India','hp'
union select 102,'US','NY'
union select 102,'US','RG'
union select 103,'China','AN'
union select 104,'Japan','PP'


SELECT distinct
        ID, country,
        stuff ( ( SELECT
                          '; ' +city
                  FROM
                          @mytable t1
                  where t1.ID = t2.ID
        FOR XML PATH ( '' ) ) , 1 , 2 , '' ) as cities
from @mytable t2

gives

ID          country    cities
101         India      delhi; hp; up
102         US         NY; RG
103         China      AN
104         Japan      PP
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

you might want to change the "'; ' + city" to "';' + city", and the third "stuff" parameter from 2 to 1 to get the format that Vishal asked for. Apart from that, have a +1.
1 Like 1 ·
Thomas: how picky, you wouldn't be a DBA would you! :)
1 Like 1 ·
+1 for solution, +1 for pedantic comment :) Well done Thomas, how would he be exceptional if we didn't help him eh?!
0 Likes 0 ·
@Kev - how did you guess? ;-) @Jack - thank you, Father!
0 Likes 0 ·

Given you can't necessarily guarantee the quality of the data, it might be worth tweaking the end of the XML PATH line as follows:

FOR XML PATH (''), type).value('.','varchar(max)'),1,2,'') as cities
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered

You might like to look at Anith Sen's comprehensive answer to this question on Simple-Talk at Concatenating Row Values in Transact-SQL

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Anith has a lot of great information in that and similar articles. I wish he'd get into the habit of doing some quantitative performance measurements, though.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

Kev's answer is excellent and Phil's reference provides you many options. If you want to consider those options in context of their effeciency, you may also want to read https://www.sqlservercentral.com/articles/performance-tuning-concatenation-functions-and-some-tuning-myths-1 by Jeff Moden. It lays out several options, but also discusses why and how efficiently they work.

Of course, in the end Jeff recommends something very similar Kev's answer as being most efficient most of the time, but it is well worth reading if you want to actually understand the options.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Heh... you've just got to know I had to give that answer a +1. ;-) Thanks for the reference, Timothy.
0 Likes 0 ·
Deepak Sharma avatar image
Deepak Sharma answered
SELECT DISTINCT T1.ID,Country, REPLACE( ( SELECT DISTINCT T2.City + ',' AS 'data()' FROM TestQ AS T2 WHERE T2.ID = T1.ID FOR XML PATH('')) + '$', ',$', '' ) AS City FROM TestQ AS T1
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.