Combining multiple rows to return one row

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining multiple rows to return one row

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_score
1 20010515 600 700
Where 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

Select StudentNo,TestDate,
MAX(Case When Subject = 'Read' then Score End)Read_Score,
MAX(Case When Subject = 'Write' then Score End)Write_Score
from @Student
Group by StudentNo,TestDate
Order by StudentNo,TestDate

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-27 : 19:53:59

OR this

Select 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources