sql server new users: crosstab?? -- sql server new users

crosstab??

r
5/23/2005 12:31:09 PM

sql server new users:

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

Re: crosstab??

Sue Hoegemeier
5/23/2005 6:08:31 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

[quoted text, click to view]

Re: crosstab??

Walter Clayton
5/23/2005 11:24:35 PM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.