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:

1 comment:

  1. Issues surrounding bin checker can never be over analyzed. Many an afternoon has been enjoyed by a family, bonding over the discussion of bin checker. Until recently considered taboo amongst polite society, it is important to remember that ‘what goes up must come down.’ It still has the power to shock those most reliant on technology, who just doesn’t like that sort of thing. Relax, sit back and gasp as I display the rich tapestries of bin checker.

    ReplyDelete

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

LinkWithin

Related Posts with Thumbnails