March 9, 2017 at 9:36 am
Currently I have this:
Client.id AS ‘ID’,
RTRIM(Users.lname) + ‘, ‘ + LTRIM(Users.fname) AS ‘Name’,
--CASE SHOWING READING, LANGUAGE OR MATH TEST TYPE
CASE
TestType.id
WHEN 1 THEN 'Reading'
WHEN 3 THEN 'Language'
WHEN 4 THEN 'Math'
ELSE ''
END AS 'Test Type',
--TEST SCORE
MAX(Testing.score) AS 'High Score'
It comes out like this, which is completely accurate.
| ID | Name | Test Type | High Score |
| 5234 | Doe, John | Language | 567 |
| 5234 | Doe, John | Math | 584 |
| 3568 | Smith, Jane | Reading | 574 |
| 3568 | Smith, Jane | Language | 568 |
| 3568 | Smith, Jane | Math | 571 |
| 88272 | Woo, Jack | Reading | 574 |
| 88272 | Woo, Jack | Math | 568 |
| 12980 | Wells, Sara | Reading | 588 |
| 12980 | Wells, Sara | Language | 591 |
| 12980 | Wells, Sara | Math | 589 |
I would prefer it come out like this, if possible. Some of the test types will not have scores and should be blank.
| ID | Name | Reading | Language | Math |
| 5234 | Doe, John | 567 | 584 | |
| 3568 | Smith, Jane | 574 | 568 | 571 |
| 88272 | Woo, Jack | 590 | 568 | |
| 12980 | Wells, Sara | 588 | 591 | 589 |
I’m not sure where to start to learn how to do this and would appreciate a push in the right direction.
Thank you in advance!
March 9, 2017 at 9:47 am
CASE TestType
WHEN 'Language' THEN HighScore
ELSE 0
END AS [Language]
, CASE TestType
WHEN 'Reading' THEN HighScore
ELSE 0
END AS Reading
, CASE TestType
WHEN 'Math' THEN HighScore
ELSE 0
END AS Math
John
March 9, 2017 at 10:26 am
As below. Or, if you need to show blanks rather than NULLs, then:
ISNULL(CAST(MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS varchar(4)), '') AS Reading,
etc.
SELECT
...
MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS Reading,
MAX(CASE WHEN TestType.id = 3 THEN Testing.score END) AS Language,
MAX(CASE WHEN TestType.id = 4 THEN Testing.score END) AS Math
GROUP BY ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 9, 2017 at 12:20 pm
ScottPletcher - Thursday, March 9, 2017 10:26 AMAs below. Or, if you need to show blanks rather than NULLs, then:
ISNULL(CAST(MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS varchar(4)), '') AS Reading,
etc.
SELECT
...
MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS Reading,
MAX(CASE WHEN TestType.id = 3 THEN Testing.score END) AS Language,
MAX(CASE WHEN TestType.id = 4 THEN Testing.score END) AS MathGROUP BY ...
Thank you Scott, this is almost identical to what I had and what I thought it would be, but my MAX was in the wrong place.
That said, this is still not returning exactly what I am looking for and any further insight would be much appreciated.
It is now showing the three columns for Reading, Language and Math and showing the appropriate scores as I had hoped, but if they have taken the test more than once, it is showing the score for each time they took it. In addition to that, it displays the user name for each test type.
| ID | NAME | Reading | Language | Math |
| 5234 | Doe, John | 562 | ||
| 5234 | Doe, John | 584 | ||
| 3568 | Smith, Jane | 489 | ||
| 3568 | Smith, Jane | 531 | ||
| 3568 | Smith, Jane | 542 | ||
| 3568 | Smith, Jane | 567 | ||
| 3568 | Smith, Jane | 541 | ||
| 3568 | Smith, Jane | 574 | ||
| 12980 | Wells, Sara | 588 | ||
| 12980 | Wells, Sara | 591 | ||
| 12980 | Wells, Sara | 589 |
John Mitchell-245523 I appreciate that, but it isn't quite what I was looking for, though if you have any other thoughts on the matter, I would be much obliged.
March 9, 2017 at 1:19 pm
I'd have to see the specific code, but it appears the GROUP BY is not complete/correct:
SELECT
ID, NAME,
MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS Reading,
MAX(CASE WHEN TestType.id = 3 THEN Testing.score END) AS Language,
MAX(CASE WHEN TestType.id = 4 THEN Testing.score END) AS Math
GROUP BY NAME, ID
ORDER BY NAME
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 9, 2017 at 4:17 pm
GROUP BY
You nailed it sir!
Something so simple and yet, I couldn't brain.
Thank you so much!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply