Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: pivot on non-aggregates

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    111

    you're a legend...

    i'll spread the word

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •