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)