December 23, 2014 at 12:51 am
Easy one, as I use it a lot.
This is a great article with many alternatives:
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 23, 2014 at 3:32 am
This was removed by the editor as SPAM
December 23, 2014 at 3:32 am
wow what a complex way of doing it.
I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)
SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
December 23, 2014 at 5:36 am
DrKiller (12/23/2014)
wow what a complex way of doing it.I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
+1 -- Very cool. Had seen the XML and used it a few times...
Was curious how even in this small example they compared
<edit>
Found this http://www.sqlservercentral.com/articles/Test+Data/61572/
</edit>
December 23, 2014 at 6:08 am
I got the good answer ( luckily as I have studied this topic 2 weeks ago and the 1st choice seems me the simplest one ) but I can't find an explanation for this error message for the 4th choice
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '2'.
Is it possible for somebody to explain why there is an incorrect syntax near '2' ( subtring has 3 parameters as logically )?
or maybe is it because we cannot put a SELECT as 1st parameter of the substring function ?
Thanks beforehand.
December 23, 2014 at 6:37 am
The problem with the fourth option is that the nested query needs to be in parentheses. So this works:
SELECT SUBSTRING( ( SELECT ', ' + productname FROM dbo.Header AS h FOR XML PATH('') ) , 2, 100)
^ ^
Interestingly though, the order of items is different!
Gerald Britton, Pluralsight courses
December 23, 2014 at 6:45 am
I love string puzzles when I have the time to do them. There are usually several ways to pull off the same result and the fun part is figuring out which way has the best performance when run over 1 row or 10M+ rows.
I recently tried writing a DelimitedSplitMax ITVF, modeled after Jeff's famous DelimitedSplit8K. Take about going down the rabbit hole! I think my brain got more twisted than the strings. I know it sounds twisted, but I actually enjoy that stuff.
December 23, 2014 at 7:27 am
Very useful combination which as many others I use a lot,
The pity is the first answer should have been
FOR XML PATH('')), 1, 2, '')
because as is, you get a blank space before the first [productname].
Cheers.
December 23, 2014 at 7:29 am
DrKiller (12/23/2014)
wow what a complex way of doing it.I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
This is a very big performance hit.
As a completely un-scientific test, I took the employee table from my system. It contains 81195 rows.
Your solution is still running after 5+ minutes.
The solution in the question runs in less than a second.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 23, 2014 at 7:45 am
DrKiller (12/23/2014)
wow what a complex way of doing it.I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
Simple, yes. Performant? Not so much.
I built a small test case using my tally TVF:
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[tally] Script Date: 12/23/2014 9:40:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[tally](@max bigint)
RETURNS TABLE
AS RETURN
(
WITH n1(n) AS (SELECT 1 FROM-- 10^1 = 10 rows
(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) v(n)),
n2(n) AS (SELECT 1 FROM n1, n1 _),-- 10^2 = 100 rows
n4(n) AS (SELECT 1 from n2, n2 _),-- 10^4 = 10,000 rows
n8(n) AS (SELECT 1 from n4, n4 _),-- 10^8 = 100,000,000 rows
n16(n) AS (SELECT 1 from n8, n8 _)-- 10^16 rows.
SELECT TOP(@max) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as n
FROM n16, n2, n1 -- 10^19 rows. NOTE: 10^18 < maxium bigint < 10^19
)
GO
Here is the test I ran, with results, using FOR XML:
declare @result varchar(max);
set statistics time on;
set statistics io on;
set @result = (select ',' + 'A' from master.dbo.tally(100000000)
for xml path(''));
GO
-- SQL Server Execution Times:
-- CPU time = 21356 ms, elapsed time = 43457 ms.
Here's the test I ran using your technique:
declare @result varchar(max);
set statistics time on;
set statistics io on;
select @result = coalesce(@result + ',' + 'A', 'A') from master.dbo.tally(10000000);
GO
I did not post results because...I killed it after 7 hours at 50 to 100% cpu.
Gerald Britton, Pluralsight courses
December 23, 2014 at 7:56 am
DrKiller (12/23/2014)
wow what a complex way of doing it.I'm usually just doing this 🙂
DECLARE @tmpString VARCHAR(MAX)SELECT @tmpString = COALESCE(@tmpString + ', ' + productname, productname) FROM Header
SELECT @tmpString
You opened Pandora's box :hehe::hehe::hehe:
Simple test against AdvertureWorks2012
SET STATISTICS TIME ON
SET STATISTICS IO ON
DECLARE @tmpString VARCHAR(MAX)
SELECT @tmpString = COALESCE(@tmpString + ', ' + [FirstName], [FirstName]) FROM AdventureWorks2012.Person.Person AS p
SELECT @tmpString
SELECT ColorCommaDelimitedList = STUFF(( SELECT ', ' + [FirstName] AS [text()] FROM AdventureWorks2012.Person.Person FOR XML PATH('') ), 1, 1, '');
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Person'. Scan count 1, logical reads 108, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 32155, physical reads 0, read-ahead reads 0, lob logical reads 1662586, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 11029 ms, elapsed time = 11564 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 36 ms.
(1 row(s) affected)
Table 'Person'. Scan count 1, logical reads 108, 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 = 199 ms.
December 23, 2014 at 10:02 am
December 23, 2014 at 10:23 am
I have rarely ever used STUFF (know I have, just can't remember when). I'll have to review it.
December 23, 2014 at 10:43 am
g.britton (12/23/2014)
The problem with the fourth option is that the nested query needs to be in parentheses. So this works:
SELECT SUBSTRING( ( SELECT ', ' + productname FROM dbo.Header AS h FOR XML PATH('') ) , 2, 100)
^ ^
Interestingly though, the order of items is different!
That's because the first one has a DISTINCT which orders the values to remove duplicates while the other one just returns them the way they were found.
None of the order is guaranteed in larger sets, though.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply