July 14, 2011 at 6:59 am
Hi all-
I posted this question earlier and received a solution to create a CTE: see--> http://qa.sqlservercentral.com/Forums/Topic1140682-392-1.aspx
CREATE TABLE [dbo].[Exams](
[examid] [int] NULL,
[entity] [varchar](255) NULL,
[value] [varchar](255) NULL,
[examrowid] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'INFO1', N'Bob', 1)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'INFO2', N'Smith', 2)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'INFO3', N'44yo', 3)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'INFO4', N'Male', 4)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'TESTType', N'Reading', 5)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'TestCode', N'R01', 6)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT1', N'58%', 7)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT2', N'28%', 8)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT3', N'33%', 9)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT4', N'12%', 10)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT5', N'89%', 11)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'TESTType', N'Writing', 12)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'TestCode', N'W01', 13)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT1', N'22%', 14)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT2', N'99%', 15)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT3', N'8%', 16)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT4', N'34%', 17)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (1, N'RESULT5', N'15%', 18)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'INFO1', N'Karen', 1)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'INFO2', N'Clark', 2)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'INFO3', N'32yo', 3)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'INFO4', N'Female', 4)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'TESTType', N'Reading', 5)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'TestCode', N'R01', 6)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT1', N'55%', 7)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT3', N'67%', 9)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT5', N'49%', 11)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'TESTType', N'Writing', 12)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'TestCode', N'W01', 13)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT1', N'2%', 14)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT2', N'5%', 15)
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT3', N'4%', 16)
The CTE works however its quite limited in that it requires the ExamRowID to be hard coded as well as an 'A' or 'B' at the end of TestType/TestCode to inidcate the student in the CTE construction. In addition it doesn't take into account there could be an unlimited number of exams,students, tests and results:
; with cte as (select max(case when entity = 'INFO1' then value else null end) as info1
,max(case when entity = 'INFO2' then value else null end) as info2
,max(case when entity = 'INFO3' then value else null end) as info3
,max(case when entity = 'INFO4' then value else null end) as info4
--- here the use of [entity] breaks down because of duplicate values
--- so we have to resort to the [examRowID]
,max(case when ExamRowID = 5 then value else null end) as TestTypeA
,max(case when ExamRowID = 6 then value else null end) as TestCodeA
,max(case when ExamRowID = 7 then value else null end) as Result1A
,max(case when ExamRowID = 8 then value else null end) as Result2A
,max(case when ExamRowID = 9 then value else null end) as Result3A
,max(case when ExamRowID = 10 then value else null end) as Result4A
,max(case when ExamRowID = 11 then value else null end) as Result5A
,max(case when ExamRowID = 12 then value else null end) as TestTypeB
,max(case when ExamRowID = 13 then value else null end) as TestCodeB
,max(case when ExamRowID = 14 then value else null end) as Result1B
,max(case when ExamRowID = 15 then value else null end) as Result2B
,max(case when ExamRowID = 16 then value else null end) as Result3B
,max(case when ExamRowID = 17 then value else null end) as Result4B
,max(case when ExamRowID = 18 then value else null end) as Result5B
from Exams
group by ExamID
) -- end of cte
select ca.Info1,ca.Info2,ca.Info3,ca.Info4,TestType,TestCode,Result1,Result2,Result3,Result4,Result5
from cte
cross apply (values
(info1,info2,info3,info4,testtypea,testcodeA,result1A,result2A,result3A,result4A,result5a),
(info1,info2,info3,info4,testtypeB,testcodeB,result1B,result2B,result3B,result4B,result5B)
) ca (Info1,Info2,Info3,Info4,TestType,TestCode,Result1,Result2,Result3,Result4,Result5)
In short, is there a way to make this example dynamic based on unlimited exams (with 1 student per exam), students, tests and results?
Many thanks for any help.
Al
July 14, 2011 at 7:02 am
It sounds like what you're looking for is "dynamic cross-tab" or "dynamic pivot". Search those terms. There are good articles on the subject.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2011 at 8:22 am
It's more complicated than that, Gus. Take a look at the original question and the solution I gave him (with warnings). The problem is that his data isn't even really suitable for pivoting or cross-tabbling in SQL. The only way you know what row goes into what column is by the rowID. While this would work for N students, it doesn't work for N exams, or N results.
__________________________________________________
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 14, 2011 at 8:35 am
Thanks guys.
I can say that the relationship between students, tests and results is deterministic according to the rowid. In other words, looping from top to bottom:
First loop would loop the Examids.
Second inner loop would loop the student /INFO1
Third inner loop beneath the 2nd would loop on TESTType
And the 4th loop beneath the 3rd would loop on RESULT1
July 14, 2011 at 8:55 am
Understood. But that is not really suitable for a set-based solution in SQL. Which is why I advised you to get some front-end coding done in a procedural language to reformat your file before turning it into a 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 14, 2011 at 9:08 am
Thanks.
Replacing the app down the road may be an option however the existing data in this structure still needs to be dealt with in the meantime--if possible.
The first column relates to a Student. An examid must contain 1 and only 1 student. Student to test, just as test
to result, is 1:N
Thanks again for the help.
July 14, 2011 at 9:34 am
You are welcome, for what my help was worth, but I am going to have to bow out at this point. While it is possible to write SQL to procedurally go through all the loops you describe (retrieving each row individually using the rowID), it would take forever to run. Perhaps someone else can provide you with a better solution.
__________________________________________________
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 14, 2011 at 9:47 am
If you need to do it at the database server, a CLR proc/function will do it much more efficiently than T-SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2011 at 2:12 pm
It isn't pretty (in form or potential performance impact) but it gives the proper result.
CAUTION: a query like this is laced with costly sorts and loops (hidden RBAR) and can bring a server to its knees, even one with a lot of horsepower...in other words I just handed you a six-shooter with a hair-trigger so don't shoot your toes off, and if you do, don't come looking for me 😀
--First loop would loop the Examids.
--Second inner loop would loop the student /INFO1
--Third inner loop beneath the 2nd would loop on TESTType
--And the 4th loop beneath the 3rd would loop on RESULT1
WITH cte
AS (
SELECT e.examid,
MAX(CASE WHEN e.entity = 'INFO1' THEN e.value
END) AS INFO1,
MAX(CASE WHEN e.entity = 'INFO2' THEN e.value
END) AS INFO2,
MAX(CASE WHEN e.entity = 'INFO3' THEN e.value
END) AS INFO3,
MAX(CASE WHEN e.entity = 'INFO4' THEN e.value
END) AS INFO4,
(
SELECT examrowid
FROM dbo.Exams
WHERE examid = e.examid
AND entity = 'TESTType'
AND value = 'Reading'
) AS reading_examrowid,
(
SELECT examrowid
FROM dbo.Exams
WHERE examid = e.examid
AND entity = 'TESTType'
AND value = 'Writing'
) AS writing_examrowid
FROM dbo.Exams e
GROUP BY e.examid
),
cte2
AS (
SELECT cte.examid,
cte.INFO1,
cte.INFO2,
cte.INFO3,
cte.INFO4,
MAX(CASE WHEN e.entity = 'TestCode' THEN e.value
END) AS TestCode,
MAX(CASE WHEN e.entity = 'RESULT1' THEN e.value
END) AS RESULT1,
MAX(CASE WHEN e.entity = 'RESULT2' THEN e.value
END) AS RESULT2,
MAX(CASE WHEN e.entity = 'RESULT3' THEN e.value
END) AS RESULT3,
MAX(CASE WHEN e.entity = 'RESULT4' THEN e.value
END) AS RESULT4,
MAX(CASE WHEN e.entity = 'RESULT5' THEN e.value
END) AS RESULT5
FROM cte
JOIN dbo.Exams e ON cte.examid = e.examid
WHERE e.examrowid >= cte.reading_examrowid
AND e.examrowid < cte.writing_examrowid
GROUP BY cte.examid,
cte.INFO1,
cte.INFO2,
cte.INFO3,
cte.INFO4
UNION ALL
SELECT cte.examid,
cte.INFO1,
cte.INFO2,
cte.INFO3,
cte.INFO4,
MAX(CASE WHEN e.entity = 'TestCode' THEN e.value
END) AS TestCode,
MAX(CASE WHEN e.entity = 'RESULT1' THEN e.value
END) AS RESULT1,
MAX(CASE WHEN e.entity = 'RESULT2' THEN e.value
END) AS RESULT2,
MAX(CASE WHEN e.entity = 'RESULT3' THEN e.value
END) AS RESULT3,
MAX(CASE WHEN e.entity = 'RESULT4' THEN e.value
END) AS RESULT4,
MAX(CASE WHEN e.entity = 'RESULT5' THEN e.value
END) AS RESULT5
FROM cte
JOIN dbo.Exams e ON cte.examid = e.examid
WHERE e.examrowid >= cte.writing_examrowid
GROUP BY cte.examid,
cte.INFO1,
cte.INFO2,
cte.INFO3,
cte.INFO4
)
SELECT *
FROM cte2
ORDER BY examid ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 2:16 pm
PS This could also be done, albeit with a lot more coding and testing, using a variation of the "Quirky Update" or the CLR (as noted) to get you a more scalable, much better performing set-based solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 18, 2011 at 2:40 pm
Does each exam at least have a fixed # of results (5, as in the example), or could one exam have more of them?
July 18, 2011 at 2:57 pm
make this example dynamic based on unlimited exams (with 1 student per exam), students, tests and results
The way I read this, guys. He wants a solution that will work for whatever data comes along, so he will never have to change it again. If you are thinking about trying to solve this with dynamic SQL, you could make one pass through the source data to get the maximum number of students, tests, and results and go from there.
__________________________________________________
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 18, 2011 at 3:07 pm
I caught that, but based on his initial question (wanting specific columns up until result 5), I assumed he meant a student could have unlimited exams, and each exam would have 5 results each (so you could have "unlimited" results only because the exams are unlimited. If so, I was just going to use a modulo operator on the ExamRowId to be able to expand for multiple exams.
But now, reading his comments about the "ExamRowId " hard code, maybe you're right.
July 18, 2011 at 3:39 pm
Same caveats as others have put up - this will likely start having perf issues.
Your best bet would be a simple cleanse routine which extracts the data and actually normalizes it on the fly. This design was not made to be handled within an RDBMS, so you end up having to help it a lot.
;with TestCountCTE as
( select *,
ROW_NUMBER() over (PARTITION by examID order by examrowid) RN,
MAX(examrowID) over (PARTITION by examID) MaxRow
from exams where entity='TestType')
select * from TestCountCTE
;with TestCountCTE as
( select *,
ROW_NUMBER() over (PARTITION by examID order by examrowid) RN
from exams where entity='TestType'),
MaxRowCTE as
(select examid,MAX(examrowid) MaxRow from exams group by examid),
TestRangeCTE as
(
select b1.examID,b1.RN , b1.examrowid startrow, ISNULL(b3.examrowid-1,MaxRowCTE.MaxRow) endRow
from TestCountCTE b1
join MaxRowCTE on b1.examid=MaxRowCTE.examid
left join TestCountCTE b3 on b1.examID=b3.examid and b1.RN=b3.RN-1
)
select examid, rn,[TESTCode],[Result1],[Result2],result3,result4,[result5]
from (
select exams.examid,rn,entity,value from exams
join TestRangeCTE on exams.examid=TestRangeCTE.examid and exams.examrowid between TestRangeCTE.startrow and TestRangeCTE.endrow) h
PIVOT
(
max(value)
for entity in ([TESTCode],[Result1],[Result2],[result3],[result4],[result5])
) unpt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 18, 2011 at 8:49 pm
Thanks to all for the help. This certainly gives me a leg up on tweaking a routine.
MUCH OBLIGED!!!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply