x

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 
more ▼

asked Jun 25, 2010 at 09:11 AM in Default

Vishal Singh gravatar image

Vishal Singh
1 1 1 1

@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!
Jun 25, 2010 at 09:45 AM ThomasRushton ♦
not guilty! although that did thow me at first and I created a csv solution
Jun 25, 2010 at 11:10 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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 
more ▼

answered Jun 25, 2010 at 09:37 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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.
Jun 25, 2010 at 09:43 AM ThomasRushton ♦
Thomas: how picky, you wouldn't be a DBA would you! :)
Jun 25, 2010 at 11:11 AM Kev Riley ♦♦
+1 for solution, +1 for pedantic comment :) Well done Thomas, how would he be exceptional if we didn't help him eh?!
Jun 25, 2010 at 11:14 AM Fatherjack ♦♦

@Kev - how did you guess? ;-)

@Jack - thank you, Father!
Jun 25, 2010 at 11:26 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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 [http://www.sqlservercentral.com/articles/61572/][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]: http://www.sqlservercentral.com/articles/61572/
more ▼

answered Jun 25, 2010 at 03:38 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Heh... you've just got to know I had to give that answer a +1. ;-) Thanks for the reference, Timothy.
Jun 30, 2010 at 03:38 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 25, 2010 at 11:28 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

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.
Jun 30, 2010 at 03:42 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Jul 05, 2010 at 08:34 AM

Deepak Sharma gravatar image

Deepak Sharma
72 3 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x45
x28

asked: Jun 25, 2010 at 09:11 AM

Seen: 2598 times

Last Updated: Jun 25, 2010 at 09:23 AM