From rows to columns - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

From rows to columns

Author

Topic

Bertin
Starting Member

7 Posts

Posted - 2011-04-04 : 06:18:13

I have a table TestResults with columns: studentID, test, score. Filled with data like

1, math, 75
1, english, 80
1, biologie, 45
2, math, 60
2, english, 90
2, biologie, 30
3, math, 45
3, english, 90
3, biologie, 72

I want to present the results in a table with one row per student so:

clientID, math, english, biologie
1, 75, 80, 45
2, 60, 90, 30
3, 45, 90, 72

can I do this with a query ?

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-04 : 06:26:53

[code]CREATE TABLE TestResults (
StudentID INT,
Subject VARCHAR(30),
Grade INT)
INSERT INTO TestResults
SELECT 1 as StudentID, 'math' as Subject, 75 as Grade UNION ALL
SELECT 1, 'english', 80 UNION ALL
SELECT 1, 'biologie', 45 UNION ALL
SELECT 2, 'math', 60 UNION ALL
SELECT 2, 'english', 90 UNION ALL
SELECT 2, 'biologie', 30 UNION ALL
SELECT 3, 'math', 45 UNION ALL
SELECT 3, 'english', 90 UNION ALL
SELECT 3, 'biologie', 72

SELECT
StudentID
,SUM(CASE WHEN [Subject] = 'math' THEN Grade END) AS mathGrade
,SUM(CASE WHEN [Subject] = 'english' THEN Grade END) AS englishGrade
,SUM(CASE WHEN [Subject] = 'biologie' THEN Grade END) AS biologieGrade
FROM TestResults
GROUP BY StudentID
[/code]

The above code would work IF you knew previously what your definitive list of subjects would be AND you wouldn't get more than one result per subject per student because it uses the SUM function. You could use MAX or MIN just as well or if a grade was altered, you could mark the old grade as deleted and then write that into the code.

Perhaps a better solution would be to use PIVOT, but it would take someone else to explain that as I'm only just learning about it myself.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources