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

avatar 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

avatar image

Kev Riley ♦♦
64k 48 61 81

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 ♦♦
  • 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/ 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.

more ▼

answered Jun 25, 2010 at 03:38 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

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

avatar image

Phil Factor
4.2k 8 23 20

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

avatar image

Deepak Sharma
72 3 3 7

(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.

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:

x1066
x50
x33

asked: Jun 25, 2010 at 09:11 AM

Seen: 6104 times

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

Copyright 2016 Redgate Software. Privacy Policy