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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s