Creating a Comma-Separated List (SQL Spackle)

Question

Creating a Comma-Separated List (SQL Spackle)

WayneS

SSC Guru

Points: 95460
More actions
January 11, 2011 at 8:11 pm

#232853

Comments posted to this topic are about the item Creating a Comma-Separated List (SQL Spackle)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 84 total)

You must be logged in to reply to this topic. Login to reply

reto.eggenberger Mr or Mrs. 500 Points: 505 More actions · Answer 1

Thank you for this tip with the STUFF function.

Is the order by in the subquery really needed? I think the for xml does it anyways. If you don't use the order by; you may use distinct to get every value only once.

WITH CTE AS

(

SELECT DISTINCT

AccountNumber

FROM #TestData

)

SELECT AccountNumber,

CommaList = STUFF((

SELECT distinct ',' + Value --<<-- to get every value only once

FROM #TestData

WHERE AccountNumber = CTE.AccountNumber

--ORDER BY Value --<<--

FOR XML PATH(''),

TYPE).value('.','varchar(max)'),1,1,'')

FROM CTE

ORDER BY AccountNumber;

greetings Reto E.

WayneS SSC Guru Points: 95460 More actions · Answer 2

reto.eggenberger (1/12/2011)
Thank you for this tip with the STUFF function.
Is the order by in the subquery really needed? I think the for xml does it anyways. If you don't use the order by; you may use distinct to get every value only once.

The order by in the subquery is used to control the ordering of the elements in the XML. If you don't use it, then there is no guarantee as to the order. You can use the distinct (or group by) to get each value once. This is independent of the order by clause.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

glock 71629 SSC Journeyman Points: 75 More actions · Answer 3

This is exactly the situation where I use PowerShell frequently. I am able to export data from whatever complex query. Idea is something like this:

Invoke-Sqlcmd -ServerInstance myServer -Database MyDb -Query "SELECT * FROM SomeTable" | Export-Csv ./result.csv -NoTypeInformation

In -Query can be any T-SQL code and results are exported natively to CSV file. I work as ConfigMgr admin and this is the way how you can really easily receive your data.

David

Dean Cochrane SSC Eights! Points: 992 More actions · Answer 4

This is useful. I only have to do this once in a while, and I've been using clumsier methods.

WayneS SSC Guru Points: 95460 More actions · Answer 5

glock 71629 (1/12/2011)
This is exactly the situation where I use PowerShell frequently. I am able to export data from whatever complex query. Idea is something like this:
Invoke-Sqlcmd -ServerInstance myServer -Database MyDb -Query "SELECT * FROM SomeTable" | Export-Csv ./result.csv -NoTypeInformation
In -Query can be any T-SQL code and results are exported natively to CSV file. I work as ConfigMgr admin and this is the way how you can really easily receive your data.
David

David,

I agree that there are several ways (BCP, PowerShell, etc.) to export the results of a query to a csv file. However, this article is about building a column of comma-separated values as part of a result set... which is completely different.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

Changing

value('.','varchar(max)'),1,1,'')

to

value('(./text())[1]','varchar(max)'),1,1,'')

appears to give a better query plan

Great article BTW.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

RHaverty 8478 SSC Veteran Points: 227 More actions · Answer 7

To all,

Here is an alternative to creating a Comma delimit list directly from a table.

-- Create a Comma Delimited List of Client Numbers (BclCode] in

-- @locBclCodeList from dbo.SomeTable

Declare @locBclCodeList Varchar(Max)

SELECT @locBclCodeList =

COALESCE(LTrim(RTrim(@locBclCodeList)) + ',' ,'') + [SomeColumn]

Fromdbo.SomeTable

-- Joins and Where clauses can go here if you need them

Print ' @locBclCodeList [' + @locBclCodeList + ']'

Rex M Haverty, MCP, DBA

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 8

Wayne, yes! More like this. This is right on target. :smooooth:

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

WayneS SSC Guru Points: 95460 More actions · Answer 9

Rex - yes, that way will work. However, please see this article[/url] for performance differences between the two methods. String manipulation has never been a strong area for MS, and your method would suffer if there is a lot of data being put into that string.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 10

Mark-101232 (1/12/2011)
Changing
value('.','varchar(max)'),1,1,'')
to
value('(./text())[1]','varchar(max)'),1,1,'')
appears to give a better query plan

Thanks for the information... I'll have to test this out.

Edit: WOW! What a difference! Identical queries, with just that one change. The way in the article gets:

(1000 row(s) affected)

Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 234 ms, elapsed time = 3175 ms.

Your suggested change gets:

(1000 row(s) affected)

Table '#TestData'. Scan count 1001, logical reads 2411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 172 ms, elapsed time = 561 ms.

Looking at the execution plans (both queries run together), the method in the article uses 99% and your solution 1%.

Thanks for the tip!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

IIRC there was a massive thread here on SSC about how to create comma delimited lists, with lots of code samples and benchmarking. Likewise one to do the reverse - take a delimited list and populate a table from it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Good stuff Wayne.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JJ B SSCertifiable Points: 5130 More actions · Answer 13

I've been using something similar to your query for a while now, but I hadn't known about the option to convert to varchar(max) and get rid of the tokens. The tokens have caused me some amount of pain and despite doing a great deal of research, I have never seen your solution anywhere. The point is: THANKS!

WayneS SSC Guru Points: 95460 More actions · Answer 14

JJ B (1/12/2011)
I've been using something similar to your query for a while now, but I hadn't known about the option to convert to varchar(max) and get rid of the tokens. The tokens have caused me some amount of pain and despite doing a great deal of research, I have never seen your solution anywhere. The point is: THANKS!

JJ - You're welcome. (I have to admit that I picked it up from a post here!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2