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 (http://blog.sqlauthority.com)