Monday, November 7, 2011

Comma seperated values (SQL)


This code generates comma separated values for an entity having multiple values. i.e. Multiple rows into a single row data value (comma seperated).
Found this code very useful for data validations in datawarehouse and thought of sharing this.
Here is the code snippet 

Create Table #TempData (EmployeeName Varchar(50), EmployeeNickName Varchar(50))



Insert Into #TempData Values('Vinuthan','VB')
Insert Into #TempData Values('Vinuthan','Vinu')
Insert Into #TempData Values('Vinuthan','Newton')

Insert Into #TempData Values('Venus','Ven')
Insert Into #TempData Values('Venus','Vens')





 Query to get comma seperated values
SELECT DISTINCT EmployeeName,
  STUFF
  (
                (
                                SELECT ',' + EmployeeNickName  FROM
                                 #TempData a WHERE b.EmployeeName = a.EmployeeName  FOR XML PATH('')
                )
                ,1,1,''
  ) EmployeeNickName
 FROM  #TempData b






Result:

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

LinkWithin

Related Posts with Thumbnails