Pages

Monday, August 8, 2011

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

No comments:

Post a Comment