Creating a comma-separated list (SQL Spackle)

  • Nice article!

  • 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

    I know this post is more than 2 years old but curiosity finally got the best of me. "Everyone" keeps recommending that PowerShell be used for these types of things but not once have I seen them identify how you could use PowerShell in a stored procedure. 😉 Also, why even bother with PowerShell when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?

    Yes, if you're trying to create a CSV file, the PowerShell solution works just fine but why would you use it instead of T-SQL if the target was also T-SQL or just a result set to an app?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • ...when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?

    How would you do it in T-SQL?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • opc.three (5/4/2013)


    ...when this is so easily done without it and it works in a stored procedure or a function without having to somehow get to the command line to do it?

    How would you do it in T-SQL?

    If you're just outputing a result set (which is the object of the article... not exporting data), then just like the article does with the [text] kicker. I'm not sure why anyone would even bring up PowerShell in such a situation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I think Wayne already called out that the comment was misplaced.

    http://www.sqlservercentral.com/Forums/FindPost1046450.aspx

    I thought you were taking things in a different direction.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • opc.three (5/5/2013)


    I think Wayne already called out that the comment was misplaced.

    http://www.sqlservercentral.com/Forums/FindPost1046450.aspx

    I thought you were taking things in a different direction.

    Yeah, but I need to call it out.

    On the other thing, I don't want to ruin Wayne's fine discussion with discussions on visceral fear. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (5/5/2013)


    opc.three (5/5/2013)


    I think Wayne already called out that the comment was misplaced.

    http://www.sqlservercentral.com/Forums/FindPost1046450.aspx

    I thought you were taking things in a different direction.

    Yeah, but I need to call it out.

    Why? What's your point?

    On the other thing, I don't want to ruin Wayne's fine discussion with discussions on visceral fear. 🙂

    No Jeff, that's not how it works. You do not get to make petty jabs and then try taking the high ground while using Wayne's (fine) article as a shield.

    Regarding "visceral fear", can you really blame me? Have you seen this thread?

    http://www.sqlservercentral.com/Forums/Topic1445014-391-1.aspx

    S******y issues aside, do you think if more people knew that every so often when you call xp_cmdshell that it might force you to restart the entire SQL Server service that more people might sart avoiding it? :Whistling:

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Sure I do and sure I will. Not here, Orlando. Let's go to another thread if you want to continue.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I know that this is a repost of an older article from 2011 and that there have been some new functionality introduced in recent version. I have used the FOR XML and STUFF() method many times and it is great, it has really saved my bacon on a few occasions. This was why I was glad to find the introduction of the STRING_AGG() function is SQL Server 2017. With this function you can replace the entire second part of the script from "WITH CTE" down with the following.

    SELECT AccountNumber,

    STRING_AGG(Value,',') AS CommaList

    FROM #TestData

    GROUP BY AccountNumber

    ORDER BY AccountNumber;

    I hope that those of you lucky enough to have an up to date system will find this easier.

  • -- swePeso
    SELECTAccountNumber,
    STRING_AGG(Value, ',') WITHIN GROUP (ORDER BY Value)
    FROM#TestData
    GROUP BYAccountNumber;

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 10 posts - 76 through 85 (of 85 total)

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