Return Table from SQL Query

Question

I have 3 tables in an SQL Server 2008 database. The first table contains users names, the second contains privileges and the last links the first two tables:

USERS (ID integer, NAME varchar(20));

PRIVS (ID integer, NAME varchar(50));

USERS_PRIVS (USERID integer, PRIVID integer);

For example, the USERS table has the following:

1, Adam
2, Benjamin
3, Chris

The PRIVS table has:

1, Add Invoice
2, Edit Invoice
3, Delete Invoice

The USERS_PRIVS table has:

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1

I am looking for a way to create an SQL query that would return something like the following:

          Add Invoice    Edit Invoice   Delete Invoice
Adam      Y              Y              Y
Benjamin  Y              Y              N
Chris     Y              N              N

Is this possible using the pivot function?

Yes. There are many examples of `PIVOT` on this site and around the web; what have you tried? — , Sep 25 '13 at 21:11
@JonofAllTrades: I tried a number of different pivot suggestions. The problem I am having is getting the column headings from the PRIVS table instead of hardcoding them in the SQL as all of the suggestions below do. — Caynadian, Sep 26 '13 at 13:13

sqlint · Answer 1 · 2013-09-25T23:31:37.520

Here's a Demo on SqlFiddle.

with cl 
as (
select u.NAME , case when p.ID = 1 then 'Y' else 'N' end 'Add Invoice',
case when p.ID = 2 then 'Y' else 'N' end 'Edit Invoice',
case when p.ID = 3 then 'Y' else 'N' end 'Delete Invoice'
from USERS u inner join USERS_PRIVS up on u.ID = up.USERID
inner join PRIVS p on up.PRIVID = p.ID  
)

select NAME, MAX([Add Invoice]) 'Add Invoice', 
MAX([Edit Invoice]) 'Edit Invoice',
MAX([Delete Invoice]) 'elete Invoice'
from cl
group by NAME

Here's a Demo on SqlFiddle.

select NAME , case when [Add Invoice] = 1 then 'Y' else 'N' end 'Add Invoice',
case when [Edit Invoice] = 1 then 'Y' else 'N' end 'Edit Invoice',
case when [Delete Invoice] = 1 then 'Y' else 'N' end 'Delete Invoice'
from (
select u.NAME, p.NAME as pname
from USERS u inner join USERS_PRIVS up on u.ID = up.USERID
inner join PRIVS p on up.PRIVID = p.ID  
) p
pivot( count(pname) for pname in ([Add Invoice], [Edit Invoice], [Delete Invoice])) as pvt

Your second script works but I was hoping to avoid hard coding each privilege name as a column heading. Is there a way to dynamically set the column names based on the privileges in the table? — Caynadian, Sep 26 '13 at 13:10

score 1 · Answer 2 · answered Sep 25 '13 at 21:35

1

Try this:

select 
    name,
    CASE [1] WHEN 1 THEN 'Y' ELSE 'N' END as 'Add Invoice', 
    CASE [2] WHEN 1 THEN 'Y' ELSE 'N' END as 'Edit Invoice', 
    CASE [3] WHEN 1 THEN 'Y' ELSE 'N' END as 'Delete Invoice'
from 
    (select name, userId, privid, 1 temp from users_privs join users on id = userid) as sourceTable
    pivot( min(temp) for privid in ([1], [2], [3])) as pivotTable

answered Sep 25 '13 at 21:35

Gruff Bunny

27,738
10
72
59

This works but I was hoping to avoid hard coding each privilege name as a column heading. Is there a way to dynamically set the column names based on the privileges in the table? – Caynadian Sep 26 '13 at 13:10

score 1 · Accepted Answer · edited May 23 '17 at 12:20

SELECT Name, [Add Invoice],[Edit Invoice],[Delete Invoice] FROM
(SELECT U.Name AS Name, P.Name AS PrivName FROM USERS_PRIVS UP
    INNER JOIN Users U ON UP.UserID = U.id
    INNER JOIN Privs P ON UP.PrivID = P.id) SB
PIVOT(
 count(SB.PrivName)
 FOR SB.PrivName in ([Add Invoice],[edit Invoice],[Delete Invoice])) AS Results

EDIT 1: You will have to use some dynamic sql then.

DECLARE @query AS NVARCHAR(MAX);
DECLARE @privColumnNames AS NVARCHAR(MAX);

select @privColumnNames = STUFF((SELECT distinct ',' + QUOTENAME(Name)
                                    FROM PRIVS FOR XML PATH(''), TYPE
                                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @query =
'SELECT *
FROM
(SELECT U.Name AS Name, P.Name AS PrivName FROM USERS_PRIVS UP
    INNER JOIN Users U ON UP.UserID = U.id
    INNER JOIN Privs P ON UP.PrivID = P.id) SB
PIVOT(
 count(SB.PrivName)
 FOR SB.PrivName in ( ' + @privColumnNames + ' )) AS Results ';

 execute(@query);

I borrowed the XML stuff from here Dynamic Pivot Columns in SQL Server

This works but I was hoping to avoid hard coding each privilege name as a column heading. Is there a way to dynamically set the column names based on the privileges in the table? — Caynadian, Sep 26 '13 at 13:12
@Caynadian see Edit 1. It uses dynamic sql to get a list of columns from your Privs Table — Josh, Sep 26 '13 at 14:45

Return Table from SQL Query

3 Answers3