Grouping data from multiple rows into single row

Often we come across situation where we need to group data from multiple rows into single row. E.g.: An Employee works for multiple projects, so in this case if columns are EMPLOYEE_NAME, PROJECT_DURATION, PROJECTS_WORKED and think that employee works for 10 projects. Then you would have 10 rows containing EMPLOYEE_NAME as same and then 10 different projects. If you now want to have a single row with Employee name and then 10 subsequent name delimited in single column, unfortunately we don’t have any function like GROUP_CONCAT as in MySQL for SQL server. But not to worry this can be achieved by using XML PATH. Below is the example .

Insert into EMPLOYEE_PROJECT(EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME)

SELECT EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME=

REPLACE(

REPLACE(

(SELECT REPLACE(PROJECT_NAME, ' ', '')

AS [data()] FROM EMPLOYEE_PROJECT_DETAILS

WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME

FOR XML PATH('')),' ',' ; '),'',' ')

FROM EMPLOYEE_PROJECT_DETAILS a WHERE EMPLOYEE_NAME IS NOT NULL GROUP BY EMPLOYEE_NAME,PROJECT_DURATION

Small query but very useful.

Happy Coding

Technorati Tags: Group,Grouping,XML PATH