We have an active usergroup in Bangalore called SQLBangalore. Whenever I get a chance to attend the local usergroup sessions (which happens often – trust me), I get to learn something every single time. In one of the recent user group meetings I had the opportunity to attend a Tips and tricks session by my good friends Balmukund and Vinod Kumar. Having friends who can stump you every now and then is an experience by itself for me.
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 sir ! very important informtion shared me