Ho to write Select query for EAV - Entity Attribute Value model

Question

I have following schema. In which

• Types represent DB Table
• TypeProperty represents Column of Table
• TypeRow represent Row of table

I want to write a select query to which I will pass single Type and it should give me all its TypeProperty, TypeRow and TypeValue that are associated with these Properties and Rows.

I will be showing this data in a web application in which user will select a Type from dropdown and application will get Properties, Rows and associated values and will show them as a complete grid.

I am using SQL Server 2014.

Can anyone help me please?

Answer 1

So, i'm going to try and take a crack at what you have been getting help with Kannan on. It sounds like you have two different queries to the database: 1) Query results of the list of 'Type(s)' for your dropdown,(You should be able to do this fairly easily) 2) query results of the list of 'Propert(ies)', 'Row(s)', and 'value(s) that match the selected 'Type' in the dropdown as a table with the properties as the header to set table.

To me it seems the easest and best way to handle this would be to get the data back using Kannan's script (probably inside of a stored procedure and maybe a view?) and create the grid in code from your back-end application, or front end client. However, if you cant, here is a script that should work or at the least get you started.

I would suggest maybe creating two stored procs, one to retrieve the data, and another to pivot using dynamic sql.

CREATE PROCEDURE dbo.EAV_GridGenerator 
    @TypeId int = 0,
    @param2 int  
AS
BEGIN TRY
DECLARE @cols varchar(max),
        @query varchar(max);
        --TODO: CLEAN UP VARIABLE NAMES THROUGHOUT
SELECT trow.TypesId, tprop.PropertyName AS [Column], trow.TypeRowId AS [RowID], tval.Value AS [Data]
INTO #TT2
FROM dbo.[Types] AS t
    JOIN dbo.TypeRow trow
        ON t.typesId = trow.typesId
        JOIN dbo.TypeValue tval
            ON tval.TypeRowsId = trow.TypeRowId
            JOIN dbo.[TypeProperty] tprop
                ON tval.TypesPropertyId = tprop.TypePropertyId
WHERE trow.TypesId = @TypeId
    --AND  t.IsActive = 1   AND  tprop.IsActive = 1 AND  trow.IsActive = 1  AND  tval.IsActive = 1--TODO: IDK but you should probably add both of these
--      AND t.IsDelete = 1  AND  tprop.IsDelete = 1 AND  trow.IsDelete = 1  AND  tval.IsDelete = 1--TODO: IDK but you should probably add both of these
ORDER BY RowID, [Column], Data


SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                                '],[' + t.[Column] 
                        FROM    #TT2 AS t 
                        --ORDER BY '],[' + t.ID 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 

SET @query = N'SELECT RowID,'+ @cols +' FROM 
(SELECT tt2.RowID,tt2.[Column] , tt2.Data FROM #tt2 AS tt2) p 
PIVOT (max([data]) FOR [Column] IN ( '+ @cols +')) 
AS pvt;' 

EXECUTE(@query)

drop table #TT2 

END TRY
BEGIN CATCH
    --TODO: PROPER CATCH
END CATCH