Saturday, September 12, 2009

Retrieve Query result(records) as Comma Separated Value in SQL Server

Its been a while that i've posted an article here. This time i am sharing a technique by which you can retrieve result from MS SQL Server in the form of Comma Separated Values(CSV). The idea is to display all the states from a particular country in CSV format.For this purpose we first create a table and name it "Country". Then we create two fields in the table, one for holding the country name and the second for holding the state name, say "countryname" and "state" respectively. After Creating the above table copy and paste the below code in your SQL Query Analyzer and Run it. Now you can see the magic!!. What is the idea behind this query?. Lets examine it.

DECLARE @s varchar(3500)

SET @s=''

SELECT @s=state +','+@s FROM Country WHERE countryname='INDIA'

SELECT @S

The keyword DECLARE is used to declare a variable named s and declare its datatype as varchar which can hold upto 3500 chars. Then we use the SET keyword to null string because if we dont assign it to null string the result will also be null. Now we use SELECT keyword to assign the result set into @s. Then at last we print our result using the last SELECT Keyword.

I know what i presented here is not a "Big thing", But every one knows nothing is so simple ever..........................

No comments:

Post a Comment

LinkWithin

Related Posts with Thumbnails