Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-27 : 18:22:19
|
I need to extract data into one record per student from a table that stores data in multiple rows. How do I do that? Please help.DECLARE @Student TABLE(StudentNo INT NOT NULL, TestDate Datetime,Subject char(10),Score int)INSERT @Student VALUES(1, ‘20010515’, ‘Read’,’600’),(1, ‘20010515’, ‘Write,’700’),I wish it to return a record with -StudentNo, Testdate, read_score, Write_score1 20010515 600 700Where I populate the scores based on the 'Subject' in my table.Niki |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-11-27 : 19:19:47
|
I think that you want to use the PIVOT operator. Look it up in BOL and see if that will work for you.=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells! |
 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-27 : 19:41:18
|
ORSelect StudentNo,TestDate,MAX(Case When Subject = 'Read' then Score End)Read_Score,MAX(Case When Subject = 'Write' then Score End)Write_Scorefrom @StudentGroup by StudentNo,TestDateOrder by StudentNo,TestDate |
 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-27 : 19:53:59
|
OR thisSelect distinct StudentNo,TestDate,MAX(Case When Subject = 'Read' then Score End) OVER (PARTITION BY StudentNo,TestDate),MAX(Case When Subject = 'Write' then Score End) OVER (PARTITION BY StudentNo,TestDate)from @Student |
 |
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-28 : 12:07:21
|
Thank you, sodeep! It worked perfectly.Niki |
 |
|
|
|
|
|
|