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

Answer 1

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.