Tip of the Day : SQL Multiple rows to Comma Separated Column
When working with SQL server and normalized data structures, there are often times when you need to flatten out the data for reporting or display purposes. There are probably several ways to do this, both in SQL server and in the presentation layer, but this is one of the methods I’ve been using, when it seems fitting to do so.
Admittedly this is probably a hack since I doubt this is how these commands were intended to be used, so if anyone has some alternative solutions, please let me know.
In addition, this example creates a table variable and manually plugs in the data. Ideally you would be running this on a properly normalized table structure using joins and etc. So please keep that in mind.
Declare @Aliases Table
(
[FirstName] varchar(50) not null,
[LastName] varchar(50) not null,
[Alias] varchar(100) not null
)
Insert @Aliases
Select 'Clark','Kent','Superman'
Union All
Select 'Clark','Kent','Kal-El'
Union All
Select 'Clark','Kent','Gangbuster'
Union All
Select 'Clark','Kent','Supernova'
Union All
Select 'Clark','Kent','Nightwing'
Union All
Select 'Peter','Parker','Spiderman'
Union all
Select 'Peter','Parker','WebSligner'
Select Distinct
[LastName],
[FirstName],
(Stuff((Select ', ' + Alias From @Aliases T2 Where T2.FirstName = T1.FirstName and T2.LastName = T2.LastName FOR XML PATH('')),1,2,'')) as Aliases
From @Aliases T1
Order By [LastName], [FirstName]
The FOR XML PATH(”) command after our select basically does the dirty work of getting the data merged into a single string. The stuff command then takes over and replaces the first comma with a blank. If you’ve never used the stuff command, takes 4 parameters. An input string, a starting index, a length, and a replacement string. It then replaces a subgstring of the input string (using the starting index and length as it’s stop and end points), with the replacement string.
Update: Sorry for the poor formatting, I need to figure out a better way to post code samples using this WordPress Template.
its good one ,
bu can somone provide me it for multiple sekect from diff table withing singke query like
a 1.2 121,232
b 4 157,528,1547
column no.2 & 3 are coming from diff tables
vishal
July 8, 2008 at 9:27 am
Wow… way overkill.
declare @colList varchar(max)
select @colList = COALESCE(@colList + ‘,’, ”) + ‘[' + column_name + ']‘
from tempdb.information_schema.columns
where …
Tim
September 20, 2008 at 12:21 am
Thanks for the suggestion Tim. I was aware of being able to combine row values into a comma separated string using a declare and a variable, but I was trying to come up with a way of doing it in way that it could be used as a Sub Query or CTE. Thanks though, you’re solution is much better if using a variable to hold the returned value is acceptable.
blaksheep
September 21, 2008 at 2:16 am
Thanks, thanks, thanks. Exactly what I needed.
I took your example and made it a udf function:
ALTER FUNCTION [dbo].[udfBibString] ( @OrderShipmentID int)
RETURNS varchar(max)
BEGIN
RETURN (Stuff ((
Select ‘, ‘ + ProductName + ‘ (‘ + Cast(Quantity as varchar(50)) + ‘)’
FROM vwOrderItemProduct where OrderShipmentID = @OrderShipmentID and ProductGroupName = ‘Race Bibs’
FOR XML PATH(”)),1,2,”))
Now I can use this function as:
SELECT dbo.udfBibString(OrderShipmentID) as Details FROM MyTable …
Thanks again.
Mark Mason
September 25, 2008 at 1:37 pm
A great job man realy amasing.
RETURN (Stuff ((
Select ‘, ‘ + ProductName + ‘ (’ + Cast(Quantity as varchar(50)) + ‘)’
FROM vwOrderItemProduct where OrderShipmentID = @OrderShipmentID and ProductGroupName = ‘Race Bibs’
FOR XML PATH(”)),1,2,”))
this was terrific
Ramón Rojo
November 28, 2008 at 6:05 pm