CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

Question

Post reply

CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

sql_learner29

SSCrazy Eights

Points: 9107
More actions
March 2, 2012 at 10:39 am

#256905

hi all...
I have a query the returns country....state...and cities
the cities are creating ore rows than required, I need to re-write my query so that the cities column instead come in single row separated by commas...
here is the data for more understanding

CREATE TABLE [dbo].[test12](
[title] [varchar](51) NULL,
[subtitle] [varchar](52) NULL,
[value] [varchar](53) NULL
)
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'PHILLY')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'PITTSBURG')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'WARREN')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'PA', 'UNION')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'EDISON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PARSIPPANY')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'METROPARK')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PRINCTON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'HAMILTON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'ISELIN')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PATERSON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'NJ', 'PARAMUS')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'mClEAN')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'FAIRFAX')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'ARLINGTON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'STAUNTON')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'NEWCITY')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'VA', 'OLDCITY')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'MIAMI')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'TAMPA')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'ORLANDO')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'JACKSONVILLE')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'NEW')
INSERT INTO test12 ( title,subtitle,value) VALUES ( 'USA', 'FL', 'OLD')
select * from test12

required output
USA | PA | PHILLY,PITTSBURG,WARREN,UNION
USA | NJ | EDISON,PARSIPPANY ,METROPARK , PRINCTON....
USA | VA | mClEAN, FAIRFAX, ARLINGTON, STAUNTON...
AND SO ON.............
hope the requirement is clear...
any help on this...
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

;with SampleDataR as

(

select *, ROW_NUMBER() over (partition by title, subtitle order by value) rownum

from test12

)

select distinct title, subtitle,(

select value

+ case when s1.rownum = (select MAX(rownum) from SampleDataR where title = s1.title and subtitle = s1.subtitle)

then '' else ',' end from SampleDataR s1

where s1.title = s2.title and s1.subtitle = s2.subtitle

for xml path(''),type).value('(.)[1]','varchar(max)') csvList

from SampleDataR s2

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Works like Charm...Thanks man roryp 96873...

Thanks again

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

Similar to Rory's , but doenst require a ROW_NUMBER

SELECT OutTab.title ,OutTab.[subtitle] ,

Cities =

STUFF ( ( SELECT ','+InrTab.value

FROM [test12] InrTab

WHERE InrTab.title = OutTab.title

AND InrTab.subtitle = OutTab.subtitle

ORDER BY InrTab.value

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)')

, 1,1,SPACE(0))

FROM [test12] OutTab

GROUP BY OutTab.title , OutTab.[subtitle] ;

ramakrishna-405526 SSC Journeyman Points: 75 More actions · Answer 4

Hi SSCrazy

Your Query is very optimised and very fast,Thanks for the good post.

It worked like a charm for me.

Thanks

rk

bornsql SSC-Addicted Points: 496 More actions · Answer 5

USE AdventureWorks

GO

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + Name

FROM Production.Product

SELECT @listStr

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

bornsql (10/8/2012)
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr

How does that solve the problem the OP posted? It looks like it would put everything on a single row instead of by country and state.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mnishar SSC Journeyman Points: 85 More actions · Answer 7

Hi you can achieve this by XML

Check this out

http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

Mnishar (7/4/2013)

Hi you can achieve this by XML

Check this out

http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html%5B/quote%5D

Hi Nnishar,

Consider using GROUP BY instead of DISTINCT. Here's your code with the WHERE clause removed to try to get the rowcounts up a bit. The first section uses DISTINCT and the second uses GROUP BY.

PRINT '========== DISTINCT ====================================================';

SET STATISTICS TIME,IO ON

SELECT Distinct col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR xml path ('')), 1, 1, '')

FROM information_schema.columns col2

SET STATISTICS TIME,IO OFF

GO

PRINT '========== GROUP BY ====================================================';

SET STATISTICS TIME,IO ON

SELECT col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR xml path ('')), 1, 1, '')

FROM information_schema.columns col2

group by table_name

SET STATISTICS TIME,IO OFF

Here are the results from the "Messages" tab... you'll see the differences immediately.

========== DISTINCT ====================================================

(30 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 134 ms.

========== GROUP BY ====================================================

(30 row(s) affected)

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 69 ms.

The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present. It likely won't matter for the OP's problem but it certainly could for other applications of the technique. Here's one way to overcome that problem using TYPE along with the VALUE conversion.

PRINT '========== GROUP BY DE-ENTITIZED =======================================';

SET STATISTICS TIME,IO ON

SELECT col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'')

FROM information_schema.columns col2

group by table_name

SET STATISTICS TIME,IO OFF

Unfortunately, the de-entitization process causes the code to use a fair bit more CPU (although it still beats DISTINCT by a fairly wide margin). For small stuff, that certainly won't seem like it matters but it will definitely matter on bigger stuff.

========== GROUP BY DE-ENTITIZED =======================================

(30 row(s) affected)

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 18 ms.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 9

Jeff Moden (7/4/2013)
The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.

I don't suppose you'd know where to get a list of those "certain characters?"

I have searched before and come up empty, not knowing the proper googling terminology to use.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Pearce SSCoach Points: 15999 More actions · Answer 10

dwain.c (7/4/2013)
Jeff Moden (7/4/2013)
The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.
I don't suppose you'd know where to get a list of those "certain characters?"
I have searched before and come up empty, not knowing the proper googling terminology to use.

http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 11

Sean Pearce (7/5/2013)
dwain.c (7/4/2013)
Jeff Moden (7/4/2013)
The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present.
I don't suppose you'd know where to get a list of those "certain characters?"
I have searched before and come up empty, not knowing the proper googling terminology to use.
http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

How embarrasing! I'm in Wiki all the time. Guess I just didn't know what to look for.

Thanks Sean!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

murthymamidim SSC Journeyman Points: 98 More actions · Answer 12

Hi Rorys,

Could u please explain the line which i didn't understood in the code which u give for the csvlist i.e

------value('(.)[1]','varchar(max)')-------

Umer Akram SSC-Insane Points: 22209 More actions · Answer 13

Umer Akram

SSC-Insane

Points: 22209

June 30, 2014 at 3:15 am

#1725775

Check this

murthymamidim SSC Journeyman Points: 98 More actions · Answer 14

murthymamidim

SSC Journeyman

Points: 98

June 30, 2014 at 3:35 am

#1725781

Hi,

Thanks for giving reply ...

Thanks,

Murthy