EAV query

• N_Muller

  SSCarpal Tunnel

  Points: 4037

  More actions

  December 6, 2013 at 10:42 am

  #301204

  I have an EAV that was given to me. It's only one table and it is as follows:

  CREATE TABLE EAV

  (

  EntityType varchar(100),

  EntityID int,

  Attribute varchar(100),

  DataType varchar(100),

  Value sql_variant

  )

  insertEAV ( EntityType, EntityID, Attribute, DataType, Value )

  values( 'Table1', 1, 'Att', 'int', '10' ),

  ( 'Table1', 1, 'MyCol', 'int', '11' ),

  ( 'Table1', 2, 'Att', 'int', '20' ),

  ( 'Table1', 2, 'MyCol', 'int', '21' ),

  ( 'Table1', 3, 'Att', 'int', '30' ),

  ( 'Table1', 3, 'MyCol', 'int', '31' )

  I'd like to build a view or function for 'Table1' that will return the following:

  EntityIDAttMyCol

  11011

  22021

  33031

  My preference is for a view because I could make it an indexed view, I've tried and searched everywhere and all I find is that EAV is a bad implementation, which I agree, but I don't have an option other than use it as is.

  How is this possible?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 6, 2013 at 11:58 am

  #1671626

  You could use a CROSS TABS[/url] approach.

  SELECT EntityID,

  MAX( CASE WHEN Attribute = 'Att' THEN Value END) AS Att,

  MAX( CASE WHEN Attribute = 'MyCol' THEN Value END) AS MyCol

  FROM EAV

  WHERE EntityType = 'Table1'

  GROUP BY EntityID

  Try to get away from there as you'll get in lots of trouble.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2