Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE.. Expand / Collapse
Author
Message
Posted Friday, March 2, 2012 10:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
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
Post #1260952
Posted Friday, March 2, 2012 10:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:09 AM
Points: 688, Visits: 5,889
;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

Post #1260959
Posted Friday, March 2, 2012 12:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
Works like Charm...Thanks man roryp 96873...
Thanks again



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

Thanks
Post #1260991
Posted Friday, March 2, 2012 12:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:13 PM
Points: 2,262, Visits: 5,404
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] ;

Post #1260997
Posted Thursday, July 12, 2012 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 12, 2012 5:06 AM
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
Post #1328797
Posted Monday, October 8, 2012 9:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 11:57 PM
Points: 104, Visits: 309
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
Post #1369905
Posted Monday, October 8, 2012 12:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,599, Visits: 31,038
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369991
Posted Thursday, July 4, 2013 1:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 1, Visits: 59
Hi you can achieve this by XML

Check this out

http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html
Post #1470333
Posted Thursday, July 4, 2013 10:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,599, Visits: 31,038
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470473
Posted Thursday, July 4, 2013 11:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 3,605, Visits: 5,198
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!
Post #1470565
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse