Creating a comma-separated list (SQL Spackle)

  • WayneS

    SSC Guru

    Points: 95341

    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

  • reto.eggenberger

    Mr or Mrs. 500

    Points: 505

    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: 95341

    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

    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: 962

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

  • WayneS

    SSC Guru

    Points: 95341

    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: 26685

    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

    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]

    From dbo.SomeTable

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

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

    Rex M Haverty, MCP, DBA

  • mtillman-921105

    SSCertifiable

    Points: 7049

    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: 95341

    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: 95341

    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

    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: 281210

    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

    SSCarpal Tunnel

    Points: 4570

    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: 95341

    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

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

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