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


Concatenating Rows


Concatenating Rows

Author
Message
Rob Fisk
Rob Fisk
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 428
Grouping by other values, possibly from another table may look like this:
/**
* Test Data
**/
DECLARE @ParentItem TABLE(ParentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentName NVARCHAR(50), ParentDate DATETIME);
DECLARE @ChildItem TABLE(ChildID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentID INT, ChildName NVARCHAR(50));
INSERT @ParentItem(ParentName,ParentDate)
SELECT 'The first one','01 jan 2009' UNION ALL
SELECT 'The second one','01 feb 2009'
INSERT @ChildItem(ParentID,ChildName)
SELECT 1,'Cat' UNION ALL
SELECT 1,'Dog' UNION ALL
SELECT 1,'Monkey' UNION ALL
SELECT 2,'Teapot' UNION ALL
SELECT 2,'Merkin' UNION ALL
SELECT 2,'Grommet'

/**
* The concatination
**/
SELECT p.ParentID, p.ParentName,
STUFF((SELECT ', ' + ChildName FROM @ChildItem WHERE ParentID = p.ParentID FOR XML PATH('')),1,2,'')
FROM @ParentItem p
GROUP BY p.ParentID, p.ParentName



_______________________________________________________
Change is inevitable... Except from a vending machine.

Jon Spink
Jon Spink
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 945
that's much better!
Joel Mansford
Joel Mansford
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 36
In my opinion the 'neatest' solution is to use a custom CLR aggregate function. Afterall this is an aggregate function. Happily MS provide a concatenate as an example or custom aggregates (first example):
http://technet.microsoft.com/en-us/library/ms131056.aspx

I appreciate that for many DBAs rolling out CLR code is a bit of a culture change but it only needs to be done once and the performance seems to be fine.
Koenraad Dendievel
Koenraad Dendievel
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 136
You're missing several other solutions

3. using UDF, coalesce and a subview

ALTER FUNCTION dbo.Concatenate_Functiebenaming (@ID_Personeelslid INT)
returns varchar(200)
as BEGIN
declare @str varchar(202)
select @str = coalesce(@str, '') + ', ' + Ambt + ISNULL(': '+Functie, '')
from [vw_Personeelsleden_Huidige_Benaming_Sub]
where [ID_Personeelslid] = @ID_Personeelslid
return substring(@str, 3, 200)
END

you call it like this
CREATE VIEW dbo.vw_Personeelsleden_Huidige_Benaming
SELECT DISTINCT ID_Personeelslid, dbo.Concatenate_Functiebenaming(ID_Personeelslid) AS Functiebenaming
FROM dbo.vw_Personeelsleden_Huidige_Benaming_Sub
GROUP BY ID_Personeelslid

4. UDF aggregate function with CLR (google it)

and

hey, here is an overview of almost all (solution 3 above is not there) + comment on drawbacks

http://www.projectdmx.com/tsql/rowconcatenate.aspx
Rob Fisk
Rob Fisk
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 428
Joel Mansford (10/14/2009)
In my opinion the 'neatest' solution is to use a custom CLR aggregate function. Afterall this is an aggregate function. Happily MS provide a concatenate as an example or custom aggregates (first example):
http://technet.microsoft.com/en-us/library/ms131056.aspx

I appreciate that for many DBAs rolling out CLR code is a bit of a culture change but it only needs to be done once and the performance seems to be fine.


The other big problem with this is if you have no idea how to actually compile the DLL or do not have the requisite software to do so.

I had a look at the example, and even with the other one showing 2 parameters I was at a complete loss as to how to alter the aggregation to take a parameter for custom separator.

_______________________________________________________
Change is inevitable... Except from a vending machine.

d viz
d viz
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 225
how about this:

declare @FinalString nvarchar(250)

SET @FinalString=''

SELECT @FinalString = COALESCE(@FinalString + ', ', '') + (CAST([name] AS nvarchar(50)))
FROM Fruit
order by [name]


SELECT REPLACE(@FinalString,'''',' ')
Joel Mansford
Joel Mansford
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 36
It's worth learning!

With regard to software I could be wrong but I believe that Visual Studio Express is all that you need. The CLR stuff comes in useful in other areas for instance with Regular Expressions or for making external web service calls.

One hacky way to support changing the separator character is to use something obscure in the code such as '¦' and then use the T-SQL REPLACE function to substitute it as required.

Personally I just have a version for comma (',') and then another that uses semi-colon (';') as that keeps the usage of the function simple.
pgoebel
pgoebel
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: 12
and what about PIVOT?
dhechle
dhechle
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 425
I was trying this the same way as Koenraad Dendievel using a function i found elsewhere, seems to work a treat with this:

CREATE FUNCTION dbo.ConcatFruit()
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + Name
FROM fruit

RETURN @Output
END
GO

SELECT TOP 1 dbo.ConcatFruit()
FROM fruit
GO
DROP FUNCTION dbo.ConcatFruit
GO
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3604 Visits: 3235
declare @l varchar(max)
,@comma varchar(2)
set @l = ''
set @comma = ''
select @l = @l + @comma + name
,@comma = ', '
from
fruit
ORDER BY name

This is the more performant!
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