|
Home > Archive > MS SQL Server New Users > May 2005 > crosstab??
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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
| |
| Sue Hoegemeier 2005-05-23, 8:24 pm |
| The following FAQ article might help you get going:
How do I create a cross-tab (or "pivot") query?
http://www.aspfaq.com/show.asp?id=2462
-Sue
On Mon, 23 May 2005 12:31:09 -0700, "r" <r@r.com> wrote:
>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
>
>
| |
| Walter Clayton 2005-05-24, 3:23 am |
| A more universal solution. There may be some kinks to work out, but I just
did something approximately like this the other day.
select IDs.ID
, max(
case T1.TestNumber
when 1 then t1.score
else null
end) Test1
, max(
case T1.TestNumber
when 2 then t1.score
else null
end) Test2
, max(
case T1.TestNumber
when 3 then t1.score
else null
end) Test3
from
(
select distinct
ID ID
from Tab
) IDs
left join
Tab t1
on (t1.ID = IDs.ID)
group by IDs.ID
order by IDs.ID
;
--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.
"r" <r@r.com> wrote in message news:utTyZ48XFHA.3572@TK2MSFTNGP12.phx.gbl...
> 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
>
>
>
|
|
|
|
|