How to pivot in SQL

Question

I am not sure if this would be called pivoting.

Data in my SQL 2005 table [CustromerRoles] is as such:

CustId  RoleId
2        4
2        3
3        4
4        1
4        2

[Roles] table:

RoleId  Role
1        Admin
2        Manager
3        Support
4        Assistant

I want to create a view such that:

SELECT * FROM [MYVIEW] will give me the data below:

The 1 & 0's will be bits so that I can display a grid with checkboxes on my UI display.

CustId  Admin Manager Support Assistant
2         0     0       1        1
3         0     0       0        1
4         1     1       0        0

So far I have no idea how to go about doing this.

Answer 1

Have you read the documentation on PIVOT in Microsoft SQL Server 2005?

SELECT CustId, 
  [1] AS Admin,
  [2] AS Manager,
  [3] AS Support,
  [4] AS Assistant
FROM (SELECT c.CustId, r.RoleId
FROM CustomerRoles c JOIN Roles r USING (RoleId)) AS s
PIVOT (
 COUNT(CustId)
 FOR RoleId IN ([1], [2], [3], [4])
) AS pvt
ORDER BY CustId;

I haven't tested the above, but just based it on the doc. This may get you started.

There doesn't seem to be a way to generate the columns dynamically. You have to hard-code them.

Answer 2

Try this:

SELECT 
    CustId, 
    SUM(ISNULL(Admin,0)) AS Admin, 
    SUM(ISNULL(Manager,0)) AS Manager, 
    SUM(ISNULL(Support,0)) AS Support, 
    SUM(ISNULL(Assistant,0)) AS Assistant
FROM
(
    SELECT cr.CustId, cr.RoleId, Role, 1 AS a
    FROM CustromerRoles cr
    INNER JOIN Roles r ON cr.RoleId = r.RoleId
) up
PIVOT (MAX(a) FOR Role IN (Admin, Manager, Support, Assistant)) AS pvt
GROUP BY CustId

Tested. It gives the same result you want.

Answer 3

PIVOT has the disadvantage that the columns must be known, because you have to provide the ids in the query. You can work around this by using dynamic SQL, i.e. generating the PIVOT query dynamically based on separate query results from the Roles table, in your case, then executing the result. This can easily be done in a stored procedure.

Example:

CREATE TABLE #CustomerRole ([CustId] int, [RoleId] int);
INSERT INTO #CustomerRole values (2, 4);
INSERT INTO #CustomerRole values (2, 3);
INSERT INTO #CustomerRole values (3, 4);
INSERT INTO #CustomerRole values (4, 1);
INSERT INTO #CustomerRole values (4, 2);

CREATE TABLE #Role ([Id] int, [Role] varchar(20));
INSERT INTO #Role values (1, 'Admin');
INSERT INTO #Role values (2, 'Manager');
INSERT INTO #Role values (3, 'Support');
INSERT INTO #Role values (4, 'Assistant');

DECLARE @RoleList nvarchar(MAX)
SELECT @RoleList = COALESCE(@RoleList + ',[' + [Role] + ']',
 '[' + [Role] + ']') 
    FROM #Role;

DECLARE @SQL Nvarchar(max);
SET @SQL = 'SELECT 
                [CustId] '  + 
                ISNULL(', ' + @RoleList , '') + ' 
                FROM #CustomerRole custrole
                inner join #Role as r
                on r.[Id] = custrole.[RoleId]
            PIVOT (count([Id]) for [Role] IN 
                (' + ISNULL(@RoleList, '[No role]') +
                 ')) as pvt;' 

EXEC sp_executesql @SQL;

drop table #Role;
drop table #CustomerRole;