July 12, 2011 at 2:52 pm
Hi all-
I have the following EAV table based on exam data which we are receiving:
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)
We would like to create derived result set that looks like this:
Columns
INFO1INFO2INFO3INFO4TESTTypeTestCodeRESULT1RESULT2RESULT3RESULT4RESULT5
records
BobSmith44yoMaleREADINGR0158%28%33%12%89%
BobSmith44yoMaleWRITINGW0122%99%8%34%15%
KarenClark32yoFemaleREADINGR0155%NULL67%NULL49%
KarenClark32yoFemaleWRITINGW012%5%4%NULLNULL
However, while I can flatten it out with selfjoins to some degree I can’t seem to group the result set as shown.
Any ideas on how this can be approached would be MOST appreciated.
Al
July 12, 2011 at 3:35 pm
al_nick (7/12/2011)
Hi all-I have the following EAV table based on exam data which we are receiving:
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)
We would like to create derived result set that looks like this:
Columns
INFO1INFO2INFO3INFO4TESTTypeTestCodeRESULT1RESULT2RESULT3RESULT4RESULT5
records
BobSmith44yoMaleREADINGR0158%28%33%12%89%
BobSmith44yoMaleWRITINGW0122%99%8%34%15%
KarenClark32yoFemaleREADINGR0155%NULL67%NULL49%
KarenClark32yoFemaleWRITINGW012%5%4%NULLNULL
However, while I can flatten it out with selfjoins to some degree I can’t seem to group the result set as shown.
Any ideas on how this can be approached would be MOST appreciated.
Al
How should we determine that this RESULT2 row...
INSERT [dbo].[Exams] ([examid], [entity], [value], [examrowid]) VALUES (2, N'RESULT2', N'5%', 15)
...is a result meant for the W01 exam and not the R01 exam?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 3:43 pm
Your problem is that there is NOTHING in your data that identifies the READING rows as compared to the WRITING rows, other than the examrowID. Assuming those rowIDs remain constant, The following should work.
; 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)
It would be far easier (and more correct from a DB standpoint) to add an additional column to identify reading results v. writing results. Populating the new column would be the responsibility of the application that populates the table initially.
__________________________________________________
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 12, 2011 at 5:04 pm
The W01 exam.
Most of the problem is face is because data is coming from a procedural type of application and the only to know how to group the results with the tests is by the examrowid which holds the order in which that EAV record was encountered and which result belongs to which exam.
July 12, 2011 at 5:58 pm
Understood. But please understand the code posted will ONLY work as long as there are NO changes to those ExamRowID numbers. If those ExamRowID numbers are variable because other entity values might be included, then the posted code WILL fail and I have no other answers for you.
In good conscience I can only recommend that you get someone to write some procedural code external to the database to give you a better schema for that 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 13, 2011 at 8:27 am
Thanks. Will give this solution a go.
Much obliged!
Al
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply