Creating a comma-separated list (SQL Spackle)

Question

Post reply

Creating a comma-separated list (SQL Spackle)

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

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

Neha05 Default port Points: 1494 More actions · Answer 1

Neha05

Default port

Points: 1494

December 25, 2012 at 8:13 am

#1571420

Nice article!

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

...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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

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)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

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)

Alex Gay SSCrazy Points: 2211 More actions · Answer 9

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 SSC-Dedicated Points: 39752 More actions · Answer 10

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

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