How to defineing a Pivot statement in T-SQL?

Question

I'm having some problems with creating a pivot query:

I have this data:

DECLARE @UserAccess TABLE
(
    userName char(255),
    project char(255),
    role char(255)
);

userName  project  role
--------  -------  ----
ado       BI       U
aga       BI       D
aga       BI       U
mim       BI       A
mim       BI       U
ado       BID      D
ado       BID      U
aga       BID      D
aga       BID      U
mim       BID      A
mim       BID      D
mim       BID      U

What I would like is this:

User    BI    BID
-----   ---   ---
ado     U     DU
aga     DU    DU
mim     AU    ADU

But I can't figure out how to construct the statement to get this.

There is no inbuilt aggregate that will concatenate the roles for you as you desire. You could create a CLR UDA and then use `PIVOT` (if order of the concatenated elements is not important) or else you will need to use `XML PATH` instead. — Martin Smith, Apr 05 '13 at 14:58

score 1 · Accepted Answer · answered Apr 05 '13 at 15:02

First, you will need to concatenate the roles into a single row, then you can apply the PIVOT function:

select username, BI, BID
from
(
  select username, project, 
    STUFF((SELECT distinct '' + t.[role]
         from UserAccess t
         where u.username = t.username
           and u.project = t.project
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') role
  from UserAccess u
) d
pivot
(
  max(role)
  for project in (BI, BID)
) piv;

See SQL Fiddle with Demo.

This can also be written using an aggregate function with a CASE expression:

;with cte as
(
  select username, project, 
    STUFF((SELECT distinct '' + t.[role]
         from UserAccess t
         where u.username = t.username
           and u.project = t.project
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') role
  from UserAccess u
)
select username,
  max(case when project = 'BI' then role end) BI,
  max(case when project = 'BID' then role end) BID
from cte
group by username;

See SQL Fiddle with Demo

How to defineing a Pivot statement in T-SQL?

1 Answers1