1. Registered User
Join Date
Jul 2006
Posts
111

I know this is a pivot table problem (or maybe you can suggest a more fit solution?) but just don't know how to get it done since i only know how to use pivots with aggregates. The problem is as follows: I have 2 tables PLAYER and TEAM and a many to many association table between the first 2 tables.
Code:
```PLAYER			PLAYER_TEAM		TEAM
PriKey	Name		PlayerKey TeamKey	PriKey	Name
1	John		1	1		1	Red
2	Tom		2	2		2	Blue
3	Bill		3	3		3	White
4	George		4	4		4	Black
5	Richard		5	5		5	Green
6	Dan		6	1
7	Matt		7	2
8	Lenny		8	3
9	Mick		9	4
10	Arnold		10	5
11	Dennis		11	1
12	Kelly		12	2
2	3
7	4
3	5
11	2
9	2
2	4
12	5
3	1
4	2```
I would like to get the following data set from my sql:
Pivot by Player
Code:
```Player	Red	Blue	White	Black	Green
John	Yes
Tom		Yes	Yes	Yes
Bill	Yes		Yes		Yes
George		Yes		Yes
Richard					Yes
Dan	Yes
Matt		Yes		Yes
Lenny			Yes
Mick		Yes		Yes
Arnold					Yes
Dennis	Yes	Yes
Kelly		Yes			Yes```
THANKS!

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```SELECT name
, MAX(Red) AS Red
, MAX(Blue) AS Blue
, MAX(White) AS White
, MAX(Black) AS Black
, MAX(Green) AS Green
FROM ( SELECT player.name
, CASE WHEN team.name = 'Red'
THEN 'Yes' ELSE NULL END AS Red
, CASE WHEN team.name = 'Blue'
THEN 'Yes' ELSE NULL END AS Blue
, CASE WHEN team.name = 'White'
THEN 'Yes' ELSE NULL END AS White
, CASE WHEN team.name = 'Black'
THEN 'Yes' ELSE NULL END AS Black
, CASE WHEN team.name = 'Green'
THEN 'Yes' ELSE NULL END AS Green
FROM team
INNER
JOIN player_team
ON player_team.teamkey = team.prikey
INNER
JOIN player
ON player.prikey = player_team.playerkey
) AS d
GROUP
BY name```

3. Registered User
Join Date
Jul 2006
Posts
111