One more simple way to pivot data in SQL Server 2005

In our previous blog posts, we have covered cross-tab reports, pivoting and un-pivoting of the data – you can access those at these links (link1, link2 and link3). In this post, we will present yet another easy way to do pivoting using the FOR XML PATH syntax in SQL Server 2005.  We will create a table variable and use it for this example:

SET NOCOUNT ON
– Prepare sample data
DECLARE    @table TABLE (Dept_ID INT, EMP_Name VARCHAR(30))

INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Jack’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘John’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Beth’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Mary’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Allen’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Diana’)
INSERT    INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Don’)

SELECT
DISTINCT    t.Dept_ID,
STUFF((SELECT ‘, ‘ + t2.EMP_NAME FROM @table AS t2 WHERE t2.Dept_ID = t.Dept_ID FOR XML PATH(”)), 1, 2, ”) AS EMP_NAMES
FROM        @table AS t
ORDER BY    t.Dept_ID

Output is shown below:

Dept_ID     EMP_NAMES
———– ————————
10          Jack, John, Beth
20          Mary, Allen, Diana, Don

The same can be achieved by many different ways – the above just represent one more way of doing it.  Personally, we never prefer using a sub-query since it will get executed once per outer record.  However, we wanted to show the concatenation capabilities by using the FOR XML PATH(”).

About these ads