Now, the scenario we are talking is simple. How can I create a comma separated string of table’s data? This is like exporting a table’s data using SQLCMD. At first this looks interesting, but can be a great learning of how SQLCMD works behind the scenes.
We will create a file for which output needs to be made. In my example, I have created a file called as Contacts-People.sql which contains the following statement.
SET NOCOUNT ON
SELECT FirstName, Lastname FROM Adventureworks2012.[Person].[Person]
WHERE Lastname = 'Ferrier'
This returns close to 16 rows on my database. Now the requirement is to get the output as a comma separated string. We can go in steps to achieve the same.
Step 1: Give the input file to SQLCMD command
C:\Temp> sqlcmd -i Contacts-People.sql
The –i option can be used to define input file.
Step 2: Make the columns width narrow to get a concise output
C:\Temp> sqlcmd -i Contacts-People.sql -W
Step 3: Next step is to add the comma separator between the columns.
C:\Temp> sqlcmd -i Contacts-People.sql -W -s,
This adds the separator.
Step 4: This is the icing on the cake. We want to remove the header so that we get ONLY the column values as part of our output.
C:\Temp> sqlcmd -i Contacts-People.sql -W -s, -h-1
There you have it. This is an easy and quick way we can generate a comma separate string of the columns from a table. I am sure there are more than one way to do this. Feel free to send the output to a flat file and name it as .csv. Do let me know if you are aware of any other method.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
While executing command it is throwing following Message-
“The server principal is not able to access database under the current security”.
please let me know how to enable access for the same.
I would use the following T-SQL using STUFF and FOR XML
DECLARE @cols NVARCHAR(MAX) =(SELECT STUFF(( SELECT
‘,’ + tbla.namecolumn
FROM MyTable AS tbla
ORDER BY ‘,’ + tbla.namecolumn
FOR XML PATH(”)
), 1, 2, ”) + ‘ ‘);
Unless, of course, I have misunderstood what you are trying to do.
PS: I use this a lot to do dynamic PIVOTs over data where I cannot predict what the columns will be beforehand, but I can construct them from data in a table. For example, if I have to count the number of patients for physicians by department within a time range, I don’t know up front which physicians saw patients during that timeframe, so I will create the string out of the physicians I read from the visit table and then pivot the visits using the string for column headings.
This is awesome.
Thanks Cory
hi Dave,
Nice tutorial!
I have question what if you the output should have header but doesnt have string “——-“.
Thanks and I appreciate you much of your help.
jonathan here!
i think it can be controlled by -h parameter.
sqlcmd -S.\SQL2016 -h -1 -Q”select @@version”
Thanks a lot. Your results always work :)
Thanks! Thanks and Thanks!
Thanks sir ! very important informtion shared me