Need help with crosstab qeury 1

[Baukep]

Back again with a newbie question.
I have a table with three fields:
speler, jaarweek, stand
1,"2006-38", 1
1,"2006-39", 2
2,"2006-38", 2
2,"2006-39", 3

What I an trying to do is to get a query that gives me:

speler, 2006-38, 2006-39
1,1,2
2,2,3

I tried CASE, IF(), IF...THEN but no result I want

Using the CASE statement resulted in:

speler, 2006-38, 2006-39
1,1,NULL
1,NULL,2
2,2,NULL
2,NULL,3

I need one row for every speler and not a row for every record.

There are of course more spelers and more weeks and using this case means a lot of typing. Somehow I think there must be a better and shorter solution.

Thanks in advance

Bauke

 

[r937]

select speler
     , sum(case when jaarweek = '2006-38'
                then stand end) as `2006-38`
     , sum(case when jaarweek = '2006-39'
                then stand end) as `2006-39`
  from daTable
group
    by speler

sorry, there is no better and shorter solution

or you could port the data into microsoft access and do it there -- access has a builtin crosstab capability

r937.com | rudy.ca

 

[Baukep]

Thank you. No need to say sorry! I have put a related problem in a new thread.

Thanks again! Stars are coming your way

Bauke