crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net