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
. .
. .
. .
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!
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.