July 19, 2011 at 7:38 am
How do you know which results are for Reading and which are for Writing? Here's a dynamic pivot that will need some tweaking to work, but it's a start
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT Entity
FROM Exams
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT [examid]
,[entity]
,[value]
FROM [dbo].[Exams] a
) t
PIVOT (min(value) FOR Entity IN (' + @ColsList + ')) PVT')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 19, 2011 at 8:16 am
Look at the previous thread he posted at the top, Mike. The way you know is by the sequence of the rowIDs, in his base table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 19, 2011 at 9:56 am
Here is my solution.
This solution assumes each test will have a max of 5 results. It further assumes that examrowid will be spaced in relation to the rows as in the example (if an "entity" is missing for a test, that row number is skipped, so there is always spacing of 7 between the tests)
If those assumptions are not true, this won't work.
;
WITH StudentCTE AS (
SELECT ExamID,
MAX(CASE WHEN entity = 'INFO1' THEN value ELSE null END) AS info1,
MAX(CASE WHEN entity = 'INFO1' THEN value ELSE null END) AS info2,
MAX(CASE WHEN entity = 'INFO1' THEN value ELSE null END) AS info3,
MAX(CASE WHEN entity = 'INFO1' THEN value ELSE null END) AS info4
FROM Exams
GROUP BY ExamID
), TestCTE AS (
SELECT ExamID,
((ExamRowID+2)/7) AS RowNumber,
MAX(CASE WHEN entity = 'TESTType' THEN value ELSE null END) AS TestType,
MAX(CASE WHEN entity = 'TestCode' THEN value ELSE null END) AS TestCode,
MAX(CASE WHEN entity = 'RESULT1' THEN value ELSE null END) AS Result1,
MAX(CASE WHEN entity = 'RESULT2' THEN value ELSE null END) AS Result2,
MAX(CASE WHEN entity = 'RESULT3' THEN value ELSE null END) AS Result3,
MAX(CASE WHEN entity = 'RESULT4' THEN value ELSE null END) AS Result4,
MAX(CASE WHEN entity = 'RESULT5' THEN value ELSE null END) AS Result5
FROM Exams
GROUP BY ExamID, ((ExamRowID+2)/7)
)
SELECT s.ExamID,
info1,
info2,
info3,
info4,
TestType,
TestCode,
Result1,
Result2,
Result3,
Result4,
Result5
FROM StudentCTE s
INNER JOIN TestCTE t ON s.ExamID = T.ExamID
WHERE RowNumber > 0
If the number of Results per test can exceed 5, and the spacing can't be assumed to be consistent, then things get nasty. If I get really bored I might try something for that, but what I'm thinking of involves a pre-query, and dynamic sql including numerous CTEs and self full outer joining. And if you really need something that nasty, it is definitely better to start from scratch than to build it. Or just do it procedurally, and when they complain about speed use it as an excuse to redesign.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply