Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..


CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

Author
Message
NoraG
NoraG
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 916
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...


---------------------------------------------------

Thanks

roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 6597

;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


NoraG
NoraG
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 916
Works like Charm...Thanks man roryp 96873...
Thanks again


---------------------------------------------------

Thanks

ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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
ramakrishna-405526
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
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
bornsql
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 332
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mnishar
Mnishar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 70
Hi you can achieve this by XML

Check this out

http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45002 Visits: 39880
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


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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search