Help trying to flatten an EAV table?

• al_nick

  Say Hey Kid

  Points: 713

  More actions

  July 12, 2011 at 1:59 pm

  #97957

  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

  INFO1 INFO2 INFO3 INFO4 TESTType TestCode RESULT1 RESULT2 RESULT3 RESULT4 RESULT5

  records

  Bob Smith 44yo Male READING R01 58% 28% 33% 12% 89%

  Bob Smith 44yo Male WRITING W01 22% 99% 8% 34% 15%

  Karen Clark 32yo Female READING R01 55% NULL 67% NULL 49%

  Karen Clark 32yo Female WRITING W01 2% 5% 4% NULL NULL

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  July 12, 2011 at 3:35 pm

  #1351919

  Duplicate post. Direct replies here: http://qa.sqlservercentral.com/Forums/Topic1140682-392-1.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato