MSSQL: Joining multiple tables with dynamic values

Question

I'm struggling to get following 3 tables into one query:

tPerson

ID          FirstName
1           'Jack'
2           'Liz'

tAttribute

ID          AttributeName
101         'LastName'
102         'Gender'

tData

PersonID    AttributeID     AttributeValue
1           101             'Nicholson'
1           102             'Male'
2           101             'Taylor'
2           102             'Female'

Important: The attributes in tAttribute are dynamic. There could be more, e.g.:

ID          AttributeName
103         'Income'
104         'MostPopularMovie'

Question: How can I write my query (or queries if neccessary), so that I get following output:

PersonID    FirstName   LastName        Gender      [otherFields]
1           'Jack'      'Nicholson'     'Male'      [otherValues]
2           'Liz'       'Taylor'        'Female'    [otherValues]

I often read "What have you tried so far?", but posting all my failed attempts using subqueries and joins wouldn't make much sense. I'm just not that secure with SQL.

Many thanks in advance.

@Tab Alleman This looks very promising, thanks! Will give it a try :) — Boris, Aug 26 '14 at 16:20
EAV in a raltional database is a design flaw all on its own. It is only indicated in VERY RARE cases when the fields cannot be determined in advance. In this rare case, a realtional database is teh wrong tool. If you genuinely need an EAV table, then use a nosql database. Otherwise, do a proper relational design and it will be faster and muiuch easier to query. — HLGEM, Aug 26 '14 at 18:01
Thanks @HLGEM. For fellow googlers: http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question (Unfortunately in my case, it's part of a 3rd party tool) — Boris, Aug 27 '14 at 09:11

score 0 · Accepted Answer · answered Aug 26 '14 at 17:16

Thanks to @Tab Alleman, I google for "SQL PIVOT" and came up with following result:

SELECT      PersonID,
            FirstName,
            [LastName],
            [Gender]
FROM (
    SELECT      tPerson.ID AS PersonID,
                tPerson.FirstName,
                tAttribute.AttributeName,
                tData.AttributeValue
    FROM        tAttribute
    INNER JOIN  tData ON (
                    tAttribute.ID = tData.AttributeID
                )
    INNER JOIN  tPerson ON (
                    tData.PersonID = tPerson.ID
                )
) AS unPivotResult
PIVOT (
    MAX(AttributeValue)
    FOR AttributeName IN ([LastName],[Gender])
) AS pivotResult

Addition: I didn't know how to get LastName and Gender dynamically via SQL, so I did that with ColdFusion, which I use for programming. It will look like this:

<!--- "local.attributes" gets generated by making another query,--->
<!--- I just wrote it statically here for this example --->
<cfset local.attributes = "[LastName],[Gender]" />

<cfquery name="local.persons">
    SELECT      PersonID,
                FirstName,
                #local.attributes#
    FROM (
        ... 
    ) AS unPivotResult
    PIVOT (
        MAX(AttributeValue)
        FOR AttributeName IN (#local.attributes#)
    ) AS pivotResult
</cfquery>

It'd be cool, if I could replace the ColdFusion part with something like SELECT AttributeName FROM tAttribute and then use that to get the brackets-definition.

MSSQL: Joining multiple tables with dynamic values

1 Answers1