CODEWITHSUNDEEP
sqlsql-servert-sql
PostureOfLearning
PostureOfLearning

Reputation: 3541

Flattening 1-to-many relationship

My current schema looks like this:

PersonType (PersonTypeID, Name, Description)

Person (PersonID,PersonTypeID, FirstName, ... )

PersonDynamicField (PersonDynamicFieldID, PersonTypeID, Name, Description, DataType, DefaultValue, ...)

PersonDynamicFieldValue (PersonDynamicFieldValueID, PersonDynamicFieldID, PersonID, Value, ...)

That is, a person is of a certain type. For example, Customer. For each PersonType, there can dynamically be added additional fields to store about a PersonType. For a Customer, we might want to add fields to PersonDynamicField such as LikesChocolate, FavoriteColor, HappinessScale, etc. The value for those fields would then be stored in PersonDynamicFieldValue.

I hope my writing makes sense.

What I would like to do, is a query that can flatten this structure and return a result looking like this:

PersonID, PersonTypeID, FirstName, LikesChocolate, FavoriteColor, HappinessScale
1, 2, Robert, 1, Green, 9
2, 2, John, 0, Orange, 5
...

I'm kind of stuck and don't really know where to even start.

Can you help?

Upvotes: 1

Views: 1935

Answers (2)

Taryn
Taryn

Reputation: 247710

In order to get the result that you want there are several ways that you can convert the rows of data into columns.

Starting in SQL Server 2005, you can use the PIVOT function. The basic structure of the code will be:

SELECT personid, persontypeid, firstname,[FavoriteColor],[HappinessScale],[LikesChocolate] 
from 
( 
  select p.personid, p.persontypeid, p.firstname, f.name fields, v.value 
  from person p 
  inner join persontype pt 
    on p.persontypeid = pt.persontypeid 
  left join PersonDynamicField f 
    on p.PersonTypeID = f.PersonTypeID 
  left join PersonDynamicFieldValue v 
    on f.PersonDynamicFieldID = v.PersonDynamicFieldID 
    and p.personid = v.personid 
) x 
pivot 
( 
  max(value) 
  for fields in ([FavoriteColor],[HappinessScale],[LikesChocolate]) 
) p;

See SQL Fiddle with Demo. The one issue that you are going to have with PIVOT is that it requires that the values being converted to columns are known at run-time. For your situation, this seems impossible since the values can change. As a result, you will have to use dynamic SQL to get the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from PersonDynamicField
                    where PersonTypeID = 2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT personid, persontypeid, firstname,' + @cols + ' 
            from 
            (
               select p.personid,
                p.persontypeid,
                p.firstname,
                f.name fields,
                v.value
              from person p
              inner join persontype pt
                on p.persontypeid = pt.persontypeid
              left join PersonDynamicField f
                on p.PersonTypeID = f.PersonTypeID
              left join PersonDynamicFieldValue v
                on f.PersonDynamicFieldID = v.PersonDynamicFieldID
                and p.personid = v.personid
            ) x
            pivot 
            (
                max(value)
                for fields in (' + @cols + ')
            ) p '


execute(@query);

See SQL Fiddle with Demo. These will give a result:

| PERSONID | PERSONTYPEID | FIRSTNAME | FAVORITECOLOR | HAPPINESSSCALE | LIKESCHOCOLATE |
-----------------------------------------------------------------------------------------
|        1 |            2 |    Robert |         Green |              9 |              1 |
|        2 |            2 |      John |        Orange |              5 |              0 |

Upvotes: 4

bitfiddler
bitfiddler

Reputation: 2115

What you want is commonly called a pivot and SQL Server has an operation that may help. Take a look at this article on MSDN for examples: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: -2

Related Questions