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 -NoTypeInformationIn -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) -
...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) -
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) -
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) -
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 84 (of 84 total)
You must be logged in to reply to this topic. Login to reply