Michal Michal - 3 months ago 11
SQL Question

SQL - how to create a dynamic matrix showing attribution values per item over time (where number of attributes varies per date)

I have:


items
which are described by a set of ids (GroupType, ID, Name)



VALUES
table which gets populated with factor values on each date so that an item gets only a certain set of factors with values per date.



FACTORS
table containing static descriptions of the factors.



Looking for:




I want to create a temporary table with a matrix showing factor values for each item per date so that one could see in user friendly way which Factors were populated on a given date (with corresponding values).

Values

Date GroupType ID Name FactorId Value
01/01/2013 1 1 A 1 10
01/01/2013 1 1 A 2 8
01/01/2013 1 1 A 3 12

01/01/2013 1 2 B 3 5
01/01/2013 1 2 B 4 6

02/01/2013 1 1 A 1 7
02/01/2013 1 1 A 2 6

02/01/2013 1 2 B 3 9
02/01/2013 1 2 B 4 9


Factors

FactorId FactorName
1 Factor1
2 Factor2
3 Factor3
4 Factor4
. .
. .
. .


temporary table Factor Values Matrix



Date Group ID Name Factor1 Factor2 Factor3 Factor4 Factor...
01/01/2013 1 1 A 10 8 12
01/01/2013 1 2 B 5 6

02/01/2013 1 1 A 7 6
02/01/2013 1 2 B 9 9


Any help is greatly appreciated!

Answer

This type of data transformation is known as a PIVOT which takes values from rows and converts it into columns.

In SQL Server 2005+, there is a function that will perform this rotation of data.

Static Pivot:

If your values will be set then you can hard-code the FactorNames into the columns by using a static pivot.

select date, grouptype, id, name, Factor1, Factor2, Factor3, Factor4
from
(
  select v.date,
    v.grouptype,
    v.id,
    v.name,
    f.factorname,
    v.value
  from [values] v
  left join factors f 
    on v.factorid = f.factorid
  -- where v.date between date1 and date2
) src
pivot
(
  max(value)
  for factorname in (Factor1, Factor2, Factor3, Factor4)
) piv;

See SQL Fiddle with Demo.

Dynamic Pivot:

In your case, you stated that you are going to have an unknown number of values. If so, then you will need to use dynamic SQL to generate a SQL string that will be executed at run-time:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FactorName) 
                    from factors
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, grouptype, id, name,' + @cols + ' from 
             (
                select v.date,
                  v.grouptype,
                  v.id,
                  v.name,
                  f.factorname,
                  v.value
                from [values] v
                left join factors f 
                  on v.factorid = f.factorid
                -- where v.date between date1 and date2
            ) x
            pivot 
            (
                max(value)
                for factorname in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

Both of these versions generate the same result:

|       DATE | GROUPTYPE | ID | NAME | FACTOR1 | FACTOR2 | FACTOR3 | FACTOR4 |
------------------------------------------------------------------------------
| 2013-01-01 |         1 |  1 |    A |      10 |       8 |      12 |  (null) |
| 2013-01-01 |         1 |  2 |    B |  (null) |  (null) |       5 |       6 |
| 2013-02-01 |         1 |  1 |    A |       7 |       6 |      11 |  (null) |
| 2013-02-01 |         1 |  1 |    B |  (null) |  (null) |       9 |       9 |

If you want to filter the results based on a date range, then you will just need to add a WHERE clause to the above queries.