Query data from meta data driven schema similar to EAV

Question

We have a data model which is similar to EAV. But, we are having limited set of attributes. In our data model, We are storing different Activities done by an individual and based on the activity type, the attributes will be varying.

We want to get the Activities and corresponding attribute/values for an individual. I have depicted the Activity, ActivityDetails table and the expected result set.

Can you please guide me in how to arrive at the expected result set from the data model ? We have to do some kind of pivoting. But, not sure on arriving at the same.

In the below example data, ClickURL is an Activity. It is having three attributes: URLAddress,ClickCount,ClickDate. The values corresponding to three attributes are : www.companyurl.com,10,20140101

The resultset should be like below:

ActivityName | Description            | URLAddress          | ClickCount    | ClickDate
-------------------------------------------------------------------------------------------
ClickURL     | Click advertisementURL | www.companyurl.com  |10             |20140101

Our Data model & Expected Result

Sample Query with Data

    CREATE TABLE [dbo].[Activity](
    [ActivityTypeID] [int] IDENTITY(1,1) NOT NULL,
    [ActivityName] VARCHAR(50) NULL,
    [Description] VARCHAR(255) NULL,
    [StringField1] [varchar](50) NULL,
    [StringField2] [varchar](50) NULL,
    [StringField3] [varchar](50) NULL,  
    [NumField1] [varchar](50) NULL,
    [NumField2] [varchar](50) NULL, 
    [DTTMField1] [varchar](50) NULL,
    [DTTMField2] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ActivityDetails](
    [ActivityDetailID] [int] IDENTITY(1,1) NOT NULL,
    [ActivityTypeID] VARCHAR(50) NULL,
    [StringField1] [varchar](50) NULL,
    [StringField2] [varchar](50) NULL,
    [StringField3] [varchar](50) NULL,  
    [NumField1] [] NULL,
    [NumField2] [int] NULL, 
    [DTTMField1] [datetime] NULL,
    [DTTMField2] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Activity](
[ActivityName]
,[Description] 
,[StringField1]
,[StringField2]
,[StringField3]
,[NumField1] 
,[NumField2] 
,[DTTMField1]
,[DTTMField2]
)
VALUES
('ClickURL','Click advertisementURL','URLAddress',NULL,NULL,'ClickCount',NULL,'clickDate',NULL)
GO

INSERT INTO [dbo].[ActivityDetails](
[ActivityTypeID]
,[StringField1]
,[StringField2]
,[StringField3]
,[NumField1] 
,[NumField2] 
,[DTTMField1]
,[DTTMField2]
)
VALUES
(1,'www.companyurl.com',NULL,NULL,10,NULL,'20140101',NULL)
GO

EAV - Entity Attribute Value model. https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model In EAV model, the attributes will for an entity will be varying. But, in our case, we have limited the number of String, Numeric, datetime attributes allowed in our schema. — Venkataraman R, Sep 19 '16 at 20:36
Please do not poste pictures. Try to set up a [MCVE](http://stackoverflow.com/help/mcve). Use `DECLARE @tbl TABLE()` to define *mock-up-tables*, fill them with sample data, show, what you've tried so far and what's your expected output. — Shnugo, Sep 19 '16 at 20:36
There are many good reasons to avoid EAV (and similar structures). Especially in cases where one can set limits to the data I'd try to define decent structures rather than generic ones... — Shnugo, Sep 19 '16 at 20:38
@Shnugo, Sorry. Added code for more clarity. In our case, we cannot avoid EAV, as we are having many different source systems. Each source system individual has got different activities and corresponding attributes. We wanted to go for relational model and avoid JSON or XML for storing these values. — Venkataraman R, Sep 19 '16 at 20:51
OK, thx for the code, but: What do you want? What is the expected output for the two rows you provided? Where do you think you have to use `PIVOT`? Is there any side / meta-data (what is *URL*, what is *date/time*, what is a number and what is plain text? Please give more input, thx! — Shnugo, Sep 19 '16 at 20:58
Can you provide more rows in example output? For every StringField1 value of Activity table you need all the records for StringField1 values of ActivityDetails table? — Kannan Kandasamy, Sep 19 '16 at 21:04
@Shnugo, I have added the expected result set to the question. I was just thinking `pivot` might be needed. but, not sure. `pivot` is needed generally in EAV scenarios. — Venkataraman R, Sep 19 '16 at 21:07
@KannanKandasamy, Just one row sample data, i want to extract for an activitytype. For an activity type, I want to get corresponding attributes and their values. In this case, I have only one string attribute, one numerical attribute, one datetime attribute. Other attributes are `NULL` for this activity type. — Venkataraman R, Sep 19 '16 at 21:09
@VenkataramanR Your expected output shows that you use data taken from your rows as column names (this forces you to use dynamic SQL!) or is your given result two rows of data? — Shnugo, Sep 19 '16 at 21:16
@Shnugo, Yes you are right. I use data taken from rows as column names. I think i have to use dynamic sql. I am looking for some other approach also. — Venkataraman R, Sep 19 '16 at 21:29

score 1 · Answer 1 · answered Sep 19 '16 at 21:41

Thanks Shnugo.I also tried something similar below.

DECLARE @ActivityTypeID int =1
DECLARE @ColList VARCHAR(MAX)
DECLARE @ActivityDetailsQuery VARCHAR(MAX)
SELECT @ColList = ''''+ ActivityName + ''' as ActivityName ,''' + Description + ''' as Description'
        +CASE when StringField1 is not null then ',StringField1 AS '+ stringField1 else '' end
       + CASE when StringField2 is not null then ',StringField2 AS '+ stringField2 else '' end
       + CASE when StringField3 is not null then ',StringField3 AS '+ StringField2 else '' end
       + CASE when NumField1 IS NOT NULL then ',NumField1 AS '+ NumField1 else '' end
       + CASE when NumField2 IS NOT NULL then ',NumField2 AS '+ NumField2 else '' end
       + CASE when DttmField1 IS NOT NULL then ',DttmField1 AS '+ DttmField1 else '' end
       + CASE when DttmField2 IS NOT NULL then ',DttmField2 AS '+ DttmField2 else '' end
from dbo.Activity
where ActivityTypeID = @ActivityTypeID  

SET @ActivityDetailsQuery = 'SELECT '+ @ColList + ' FROM dbo.ActivityDetails WHERE ActivityTypeID = ' + CAST(@ActivityTypeID as VARCHAR(10))
EXEC(@ActivityDetailsQuery)

Yeah, that is roughly the same idea as mine! Happy Coding and +1 — Shnugo, Sep 19 '16 at 21:43

score 0 · Accepted Answer · answered Sep 19 '16 at 21:35

I must admit that I doubt, that your approach is the best one could find... But - at least - you could try this:

Attention: For testing purpose I drop the created table at the end. Carefull with real data!

--Your tables

 CREATE TABLE [dbo].[Activity](
    [ActivityTypeID] [int] IDENTITY(1,1) NOT NULL,
    [ActivityName] VARCHAR(50) NULL,
    [Description] VARCHAR(255) NULL,
    [StringField1] [varchar](50) NULL,
    [StringField2] [varchar](50) NULL,
    [StringField3] [varchar](50) NULL,  
    [NumField1] [varchar](50) NULL,
    [NumField2] [varchar](50) NULL, 
    [DTTMField1] [varchar](50) NULL,
    [DTTMField2] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ActivityDetails](
    [ActivityDetailID] [int] IDENTITY(1,1) NOT NULL,
    [ActivityTypeID] VARCHAR(50) NULL,
    [StringField1] [varchar](50) NULL,
    [StringField2] [varchar](50) NULL,
    [StringField3] [varchar](50) NULL,  
    [NumField1] [int] NULL,
    [NumField2] [int] NULL, 
    [DTTMField1] [datetime] NULL,
    [DTTMField2] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Activity](
[ActivityName]
,[Description] 
,[StringField1]
,[StringField2]
,[StringField3]
,[NumField1] 
,[NumField2] 
,[DTTMField1]
,[DTTMField2]
)
VALUES
('ClickURL','Click advertisementURL','URLAddress',NULL,NULL,'ClickCount',NULL,'clickDate',NULL)
GO

INSERT INTO [dbo].[ActivityDetails](
[ActivityTypeID]
,[StringField1]
,[StringField2]
,[StringField3]
,[NumField1] 
,[NumField2] 
,[DTTMField1]
,[DTTMField2]
)
VALUES
(1,'www.companyurl.com',NULL,NULL,10,NULL,'20140101',NULL)
GO

--I declare a variable and create the statement dynamically

DECLARE @cmd VARCHAR(MAX)=
(
    SELECT
    'SELECT ''' + a.ActivityName + ''' AS ActivityName'
    +   ',''' + a.Description + ''' AS Description'
    +   ISNULL(',''' + ad.StringField1 +''' AS ' + QUOTENAME(a.StringField1),'')
    +   ISNULL(',''' + ad.StringField2 +''' AS ' + QUOTENAME(a.StringField2),'')
    +   ISNULL(',''' + ad.StringField3 +''' AS ' + QUOTENAME(a.StringField3),'')
    +   ISNULL(',' + CAST(ad.NumField1 AS VARCHAR(100)) +' AS ' + QUOTENAME(a.NumField1),'')
    +   ISNULL(',' + CAST(ad.NumField2 AS VARCHAR(100)) +' AS ' + QUOTENAME(a.NumField2),'')
    +   ISNULL(',CAST(''' + CONVERT(VARCHAR(100),ad.DTTMField1,126) +''' AS DATETIME) AS ' + QUOTENAME(a.DTTMField1),'')
    +   ISNULL(',CAST(''' + CONVERT(VARCHAR(100),ad.DTTMField2,126) +''' AS DATETIME) AS ' + QUOTENAME(a.DTTMField1),'')
    FROM Activity AS a
    INNER JOIN ActivityDetails AS ad ON a.ActivityTypeID=ad.ActivityTypeID
)

--Here the statement is executed

EXEC(@cmd)
GO

--Clean-Up (Carefull with real data!)

--DROP TABLE ActivityDetails;
--DROP TABLE Activity;

The created statement reads like this:

SELECT 'ClickURL' AS ActivityName
      ,'Click advertisementURL' AS Description
      ,'www.companyurl.com' AS [URLAddress]
      ,10 AS [ClickCount]
      ,CAST('2014-01-01T00:00:00' AS DATETIME) AS [clickDate]

The trick is, that a concatenated string is NULL all over, if one of its elements is NULL. That is the reason, why the created statement will contain the columns used only.

I have accepted your answer. I also tried something similar. I have put that as another one for additional reference. — Venkataraman R, Sep 19 '16 at 21:41

Query data from meta data driven schema similar to EAV

2 Answers2