October 16, 2009 at 8:59 am
This method:
DECLARE @var varchar(8000)
SET @var = ''
SELECT @var = @var + FieldName + '/'
FROM Table
SELECT @Var
works fine for even multiple entities, just create a view first. I've been using this for years and happy about it
October 16, 2009 at 12:50 pm
sonphan11 (10/16/2009)
This method:DECLARE @var varchar(8000)
SET @var = ''
SELECT @var = @var + FieldName + '/'
FROM Table
SELECT @Var
works fine for even multiple entities, just create a view first. I've been using this for years and happy about it
Create a view from what? Would you post the view you're talking about?
Also, the code above will leave a trailing delimiter. Ok if that's what you need. It also does the whole table all at once... again, ok but only if that's what you need.
And, "Welcome Aboard" to you, too! This thread seems to really be drawing out the first timers. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2009 at 1:05 pm
1) What I meant was no particular view. This post was to reply back to a comment saying this method works only on one entity. I assumed the entity here meant "table", right? If so, multiple entities would mean multiple tables and if you wanted to concatenate their rows, you'd have to first create a view that joins all essential tables, then run the sql code to concatenate rows. One assumption here: this is to get all rows of a particular column, not multiple columns. If you have 2 tables A and B and you want to get all rows from A.Column_1 and all rows from B.Column_1 concatenated, you can just create a UNION view of the tables, something like:
CREATE VIEW To_Concatenate
AS
SELECT A.column_1 FROM Table_A A
UNION
SELECT B.Column_1 FROM Table_B B
Then replace Table in the sql code with the view name (To_Concatenate)
2) I'm not sure I understand your concern with this approach working on only the whole table. You can always create filter on your table to select the rows you're interested, then concatenate.
Hope I understood your questions correctly and these lines answer your questions. Otherwise, please let me know. Thanks
October 16, 2009 at 2:18 pm
I recommend reading prior posts to get some background how this thread has been progressing. I noticed the solution you posted is a direct quote from the first post to this article. But, by about the third or fourth post, the question was asked about multiple entities ("entity" meaning column, not table).
One later poster illustrated the question as follows:
What about if you had rows that you wanted to aggregate like this:
ID NAME
101 Apple
101 Banana
102 Orange
102 Melon
102 Grape
And wanted a result set like:
ID NAMES
101 Apple, Banana
102 Orange, Melon, Grape
Although the post was a nice clear illustration of the question, 6 possible solutions, complete with benchmarking test code, had already been posted that answered it!
It's tempting when reading an article to jump in and post your thoughts but without taking the time to first read and understand what others have posted, you run the risk of looking silly by repeating a question and/or solution that's already been presented.
October 16, 2009 at 3:05 pm
Well, this is my first time here and I thought I was just trying to comment on one particular piece of code I happened to see in front of me with the assumption that I was reply to that ONLY PERSON who posted that piece of code. My only intention was to second the idea in the post
Instead, I got some lecture from some guy, who thinks he's one of the smartest people on earth alive asking me to read the whole thread, not tempting, etc.... If you read my post closely, I was careful enough to put "...if I understood your question correctly....". So please don't call someone "silly" here ok?
And FYI, I've been in this field long enough to understand what i'm doing, and I just don't have time for showing off. If you think you're the best person out there to handle all kinds of questions, then go wild, my friend!
I can't believe this is my first time in this room it already disappointed me!
October 16, 2009 at 3:30 pm
I understand in this discussion as you mentioned, "entity" = "column", but I think the more logical term is "attribute", not "entity" (as mainly used to refer to table in database world)
Regards
October 16, 2009 at 7:15 pm
For those of you still following this thread, I wanted to touch upon something I alluded to in the article. When I tested the performance of my two solutions, I sometimes found wildly unpredictable times with the XML solution. For example, a 5 row concatenation could take 30 seconds using the XML method while completing instantly using the recursive CTE.
I found that when another user's query was generating lots of XML output the performance of even a trivial concatenation could sometimes become terrible. This lead me to suspect that there was some sort of internal contention going on around the component responsible for generating the XML.
To be fair, I'm not an expert on the inner workings of SQL Server and this was experienced on a 2005 instance (we have since moved on to 2008), but I felt it deserved a disclaimer.
Again, thanks to those who provided benchmarks!
October 16, 2009 at 10:10 pm
Jeff,
A more generic XML solution, which doesn't suffer from the entitization problem is:
;WITH Drink (name, id)
AS (
SELECT N'Beer', 101 UNION ALL
SELECT N'Wine', 102 UNION ALL
SELECT N'Gin & Tonic', 103 UNION ALL
SELECT N'?????', 104 UNION ALL
SELECT N'Cognac', 105
),
CSV (xml_data)
AS (
SELECT ',' + name
FROM Drink
ORDER BY
id
FOR XML PATH(''), TYPE
)
SELECT result = STUFF(xml_data.value('.[1]', 'NVARCHAR(MAX)'), 1, 1, N'')
FROM CSV;
edit: aw carp - done it again! There are four pages of comments, not just the last one that the link took me to!
Oh well, never mind.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2009 at 12:00 am
sonphan11 (10/16/2009)
1) What I meant was no particular view. This post was to reply back to a comment saying this method works only on one entity. I assumed the entity here meant "table", right? If so, multiple entities would mean multiple tables and if you wanted to concatenate their rows, you'd have to first create a view that joins all essential tables, then run the sql code to concatenate rows. One assumption here: this is to get all rows of a particular column, not multiple columns. If you have 2 tables A and B and you want to get all rows from A.Column_1 and all rows from B.Column_1 concatenated, you can just create a UNION view of the tables, something like:CREATE VIEW To_Concatenate
AS
SELECT A.column_1 FROM Table_A A
UNION
SELECT B.Column_1 FROM Table_B B
Then replace Table in the sql code with the view name (To_Concatenate)
2) I'm not sure I understand your concern with this approach working on only the whole table. You can always create filter on your table to select the rows you're interested, then concatenate.
Hope I understood your questions correctly and these lines answer your questions. Otherwise, please let me know. Thanks
Ah. Got it. Thanks for the feedback. And my concern about the approach working only on the whole table is that a lot of folks want it to work on the whole table but in sections. I was just trying to make people understand that something else may be necessary. As you say, a "filter".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2009 at 12:09 am
Paul White (10/16/2009)
edit: aw carp - done it again! There are four pages of comments, not just the last one that the link took me to!Oh well, never mind.
Heh... not to worry. There's nothing like confirmation of code. Between you and Adam, I've learned something today. You should see how many times the COALESCE version has been listed on this thread. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2009 at 1:06 am
Jeff Moden (10/17/2009)
Heh... not to worry. There's nothing like confirmation of code. Between you and Adam, I've learned something today. You should see how many times the COALESCE version has been listed on this thread. 😛
Thanks Jeff. Yeah I've been through all the posts now, so I see what you mean!
I have to say it is a source of some frustration for me that the 'best' way to concatenate strings in SQL Server involves such a hack. Surely, if Microsoft's intention with FOR XML PATH('') was to provide a fast way to perform concatenation, they could have made it a bit more natural, concise, and ultimately even more performant - perhaps by providing a proper aggregate function?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 17, 2009 at 9:20 am
msam77-503326 (10/15/2009)
Below is simple sql that does thatdeclare @res varchar(Max)
select @res = coalesce(@res + ',', '') + [name]
from
(
select 'Apple' as name, 101 as id union all
select 'Banana' as name, 102 as id union all
select 'Orange' as name, 103 as id union all
select 'Melon' as name, 104 as id union all
select 'Grape' as name, 105 as id
) a
select @res
The best one always is my version without coalesce, replace and without removing final comma:
create table fruit(name varchar(20))
insert into fruit SELECT 'apple'
union all select 'banana'
union all select 'orange'
union all select 'melon'
union all select 'grape'
declare
@l varchar(8000)
,@comma varchar(2)
set @l = ''
set @comma = ''
select @l = @l + @comma + name
,@comma = ', '
from fruit
ORDER BY name
select @l
October 18, 2009 at 12:11 am
Hey Carlo...would you please define 'best' for me? 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 18, 2009 at 2:59 am
Yours is pretty cool, but what do I need to modify if I want to get rid of duplicates? 🙂
October 18, 2009 at 9:56 am
Paul White (10/17/2009)
I have to say it is a source of some frustration for me that the 'best' way to concatenate strings in SQL Server involves such a hack. Surely, if Microsoft's intention with FOR XML PATH('') was to provide a fast way to perform concatenation, they could have made it a bit more natural, concise, and ultimately even more performant - perhaps by providing a proper aggregate function?
Absolutely agreed... and a proper "split" function and proper "sum" function and a proper "pivot" function and a proper cursor that resolves to set based code and... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 91 through 105 (of 159 total)
You must be logged in to reply to this topic. Login to reply