Pivot Data

[samtwilliams]

Hi all,

 

I have two tables which i have joined but i want to pivot the data in one table, example below.

 

Current table1;

 

wl_id|forename|surname

7|Sam|Williams

 

Current table2 (the one i need to pivot);

 

wl_id|result|week_number

7|win|1

7|win|2

7|loss|3

7|half|4

7|win|5

 

I would like to display all the data like this

 

wl_id|forename|surname|1|2|3|4|5

7|Sam|Williams|win|win|loss|half|win

 

The number being the week_number.

 

Can anyone provide me an example?

 

Sam

[samtwilliams]

I have now got a little further except in the pivot it only displays one result when it should be displaying a result under each week;

 

select winter_league_rnds.wl_id, winter_league.forename, winter_league.surname,
case when week='1' then result else ' ' end as WEEK1,
case when week='2' then result else ' ' end as WEEK2,
case when week='3' then result else ' ' end as WEEK3,
case when week='4' then result else ' ' end as WEEK4,

case when week='5' then result else ' ' end as WEEK5,
case when week='6' then result else ' ' end as WEEK6,
case when week='7' then result else ' ' end as WEEK7,
case when week='8' then result else ' ' end as WEEK8,
case when week='9' then result else ' ' end as WEEK9,
case when week='10' then result else ' ' end as WEEK10,
case when week='11' then result else ' ' end as WEEK11,
case when week='12' then result else ' ' end as WEEK12,
case when week='13' then result else ' ' end as WEEK13,
case when week='14' then result else ' ' end as WEEK14,
case when week='15' then result else ' ' end as WEEK15,
case when week='16' then result else ' ' end as WEEK16,
case when week='17' then result else ' ' end as WEEK17,
case when week='18' then result else ' ' end as WEEK18,
case when week='19' then result else ' ' end as WEEK19,
case when week='20' then result else ' ' end as WEEK20

from winter_league_rnds LEFT JOIN (winter_league) ON (winter_league.wl_id = winter_league_rnds.wl_id)

group by wl_id ORDER BY surname ASC

[phpchamps]

hope this helps you

 

http://lists.mysql.com/mysql/182361

[samtwilliams]

Thanks for your reply phpchamps,

 

I have looked at this example already, the only difference is that i am not summing or counting anything, i just want to list the result under each week.

 

I now have this which i think is close but still only returning the first result when a lot of players have played in all weeks it just lists the first result in the first week.

 

select winter_league_rnds.wl_id AS WLID, winter_league.forename, winter_league.surname,
CASE WHEN week=1 THEN (SELECT result FROM winter_league_rnds WHERE week = 1 AND winter_league_rnds.wl_id = WLID) ELSE ' ' END AS WEEK1,
CASE WHEN week=2 THEN (SELECT result FROM winter_league_rnds WHERE week = 2 AND winter_league_rnds.wl_id = WLID) ELSE ' ' END AS WEEK2,
CASE WHEN week=3 THEN (SELECT result FROM winter_league_rnds WHERE week = 3 AND winter_league_rnds.wl_id = WLID) ELSE ' ' END AS WEEK3

from winter_league_rnds LEFT JOIN (winter_league) ON (winter_league.wl_id = winter_league_rnds.wl_id)


group by WLID ORDER BY surname ASC LIMIT 155

 

Sam

[fenway]

A single column can only contain a single value.