How do I create a Pivot query for this simple case?

Question

I have a very simple query that I need pivoted, but I haven't got a clue how to turn it into a pivot table from what I've read in the literature. Everything I've read involves hard-coded columns or is too complex. It has to be simpler, or I just can't grasp CTE's.

The query is a list of user names and clients they have access to. So I have something like:

user  client
1 a
1 b
1 c
2 a
2 d
3 a
3 d
3 e
3 f

The total number of clients could be around 20 or so. Some users can access all clients, others only one or two. What I want to see is:

user a b c d e f
1 x x x
2 x   x
3 x   x x x

This involves three tables: a user table, a client table, and a cross-reference table (permissions) that unites users with their clients.

I've tried something like the following, but it obviously doesn't work (or even compile).

with usersclients(user_name, clients, client_code)
as
(
 select users.user_name
 from clients cli
  left join
  (select u.user_id, user_name, c.client_id, client_code, client_name 
   from permissions p inner join clients c on p.client_id = c.client_id
   inner join users u on u.user_id = p.user_id
  ) user
  on user.client_id = cli.client_id
)
select *
from usersclients
pivot (max(client_code) for client_code in (select client_code from clients)) as P

Any clues most appreciated!

Answer 1

This doesn't format very well. How do you format the question properly?

Answer 2

I believe SQL server requires you to explicitly name each column in your pivot. So, the following snippet from your original query is not allowed:

for client_code in (select client_code from clients)

You would have to name each client code explicitly.

Edit: Here is a sample pivot to fit your example:

WITH Permit (Usr, Client) AS
(
    SELECT 1, 'a' UNION ALL
    SELECT 1, 'b' UNION ALL
    SELECT 1, 'c' UNION ALL
    SELECT 2, 'a' UNION ALL
    SELECT 2, 'd' UNION ALL
    SELECT 3, 'a' UNION ALL
    SELECT 3, 'd' UNION ALL
    SELECT 3, 'e' UNION ALL
    SELECT 3, 'f'
)
SELECT p.*
FROM Permit
    PIVOT (MAX(Client) FOR Client IN (a, b, c, d, e, f)) p

Edit: Here is a dynamic SQL option; you might put this in a TVF:

--source table
CREATE TABLE #Permit (Usr int, Client char(1));
INSERT INTO #Permit (Usr, Client)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 2, 'a' UNION ALL
SELECT 2, 'd' UNION ALL
SELECT 3, 'a' UNION ALL
SELECT 3, 'd' UNION ALL
SELECT 3, 'e' UNION ALL
SELECT 3, 'f';


DECLARE @Command nvarchar(max);
SET @Command = '';

--prepare the list of columns
SELECT @Command = @Command + ', ' + CONVERT(nvarchar(10), Client)
FROM (SELECT DISTINCT Client FROM #Permit) x;

--chop the extra leading comma off
SET @Command = SUBSTRING(@Command, 3, LEN(@Command));

--prepare the rest of the pivot command
SET @Command = N'
SELECT p.*
FROM #Permit
    PIVOT (MAX(Client) FOR Client IN (' + @Command + ')) p';

--execute the command
EXEC sp_executesql @Command;


DROP TABLE #Permit;