August 24, 2012 at 7:48 am
Hi
i need to display column values as a row,
create table #temp1
(
iSchoolYearCode int,
cStudentID char(9),
iMathScaleScore int,
iReadingScaleScore int,
iWritingScaleScore int,
iTotalScore int
)
insert into #temp1 values (2004,'002445166',28,34,32,94)
desired output
iSchoolYearCodecStudentIDSubjectScore
2004002445166Math28
Reading34
Writing32
Total94
August 24, 2012 at 8:13 am
Try this:
SELECT
iSchoolYearCode
,cStudentID
,[Subject] = 'Math'
,Score = iMathScaleScore
FROM #temp1
UNION ALL
SELECT
iSchoolYearCode
,cStudentID
,[Subject] = 'Reading'
,Score = iReadingScaleScore
FROM #temp1
UNION ALL
SELECT
iSchoolYearCode
,cStudentID
,[Subject] = 'Writing'
,Score = iWritingScaleScore
FROM #temp1
UNION ALL
SELECT
iSchoolYearCode
,cStudentID
,[Subject] = 'Total'
,Score = iTotalScore
FROM #temp1
--Vadim R.
August 24, 2012 at 8:15 am
Cross apply values technique:
insert into #temp1 values (2004,'002445166',28,34,32,94)
insert into #temp1 values (2005,'002445166',19,35,66,75)
SELECT iSchoolYearCode,cStudentID,[Subject],Score
FROM #temp1
CROSS APPLY (
VALUES
('Math', iMathScaleScore)
,('Reading', iReadingScaleScore)
,('Writing', iWritingScaleScore)
,('Total', iTotalScore)
) a([Subject], Score);
iSchoolYearCode cStudentID Subject Score
--------------- ---------- ------- -----------
2004 002445166 Math 28
2004 002445166 Reading 34
2004 002445166 Writing 32
2004 002445166 Total 94
2005 002445166 Math 19
2005 002445166 Reading 35
2005 002445166 Writing 66
2005 002445166 Total 75
(8 row(s) affected)
August 24, 2012 at 8:16 am
Edit: Someone else posted the solution before me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply