Comma Separated Value(CSV) String

        It is sometimes found that to make a Comma Separated Value(CSV) strings, Developers do a bit hard work and create functions with applying some longer logics and get the output for the same. But better if you can use a simple and short logic as below :

Let's sat we have a table Products as :
  

UserID
UserName
6001581
Amit
6001582
Sumit
6001583
Vineet
6001584
Muthu
6001585
Ranjan
6001586
Sudheer

  and  like to get a comma delimited list of UserID then we will write code as :   

   DECLARE @listStr VARCHAR(MAX)


   SELECT @listStr = COALESCE(@listStr+',' ,'') + UserID


   FROM  Products


    

   SELECT @listStr

Now execute above code and you will get a CSV string -

6001581,6001582,6001583,6001584,6001585,6001586

 In the above output you will have a string of UserID's without enclosed in single quotes..
So in order to get the same output with single quotes do as below :

   DECLARE @listStr VARCHAR(MAX)
   SELECT @listStr = COALESCE(@listStr+',' ,'') + '''' + UserID + ''''
   FROM  Products

   SELECT @listStr

Now execute above code and you will get a CSV string -

'6001581','6001582','6001583','6001584','6001585','6001586'

----Have a nice day.........

CSV String with Count of Items

     It may required to get a CSV string and Count of Items which included in the string. In this case, all the columns will be queried normally and the CSV column will be made through a subquery.

 Take an example of a table UserMaster as below :

CREATE TABLE UserMaster

(

 UserID     
     VARCHAR(20)

,JoiningMonth    INT

,JoiningYear     INT

)




---- Insert data
in UserMaster




INSERT INTO UserMaster

SELECT 'U001',10,2011

UNION

SELECT 'U002',10,2011

UNION

SELECT 'U003',5,2012

UNION

SELECT 'U004',5,2012

UNION

SELECT 'U005',5,2012

UNION

SELECT 'U006',6,2012

UNION

SELECT 'U007',6,2012

UNION

SELECT 'U008',6,2012

UNION

SELECT 'U009',6,2012

    Now we like to get CSV string of UserID, with its counts Grouping by JoiningMonth and JoiningYear.

 To get the task done, write as below :

SELECT  COUNT(1) AS COUNTS

       ,JoiningMonth

       ,JoiningYear

       ,STUFF((SELECT ','+''''+CAST(UserID AS VARCHAR(MAX))+''''

              FROM UserMaster T (NOLOCK)

              WHERE T.JoiningMonth = S.JoiningMonth

                  AND T.JoiningYear = S.JoiningYear

              FOR XML PATH('')),1,1,'') AS CSV

FROM  UserMaster S (NOLOCK)

GROUP BY JoiningMonth,JoiningYear







---- Output is :




COUNTS  JoiningMonth  JoiningYear  CSV

2       10            2011         'U001','U002'

3       5             2012         'U003','U004','U005'

4       6             2012         'U006','U007','U008','U009'

Reference: Govind Badkur(http://govindbadkur.blogspot.com)