SQL - как создать динамическую матрицу, показывающую значения атрибуции на элемент во времени (где количество атрибутов меняется на дату)

Question

Я имею:
items которые описываются набором идентификаторов (GroupType, ID, Name)

VALUES таблица, которая заполняется значениями факторов на каждую дату, так что элемент получает только определенный набор факторов со значениями на дату.

FACTORS таблица, содержащая статические описания факторов.

Находясь в поиске:

Я хочу создать временную таблицу с матрицей, показывающей значения факторов для каждого элемента на дату, чтобы можно было в удобной для пользователя форме увидеть, какие факторы были заполнены на определенную дату (с соответствующими значениями).

Ценности

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

факторы

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

Любая помощь очень ценится!

Answer 1

Этот тип преобразования данных известен как PIVOT который берет значения из строк и преобразует их в столбцы.

В SQL Server 2005+ есть функция, которая будет выполнять эту ротацию данных.

Статический Пивот:

Если ваши значения будут установлены, то вы можете жестко FactorNames в столбцы с помощью статического поворота.

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;

Смотрите SQL Fiddle с демонстрацией.

Динамический Пивот:

В вашем случае вы заявили, что у вас будет неизвестное количество значений. Если это так, то вам нужно будет использовать динамический SQL для генерации строки SQL, которая будет выполняться во время выполнения:

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)

Смотрите SQL Fiddle с демонстрацией.

Обе эти версии дают одинаковый результат:

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

Если вы хотите отфильтровать результаты по диапазону дат, вам просто нужно добавить WHERE пункт к вышеуказанным запросам.

Answer 2

Похоже, вы просто пытаетесь превратить строки в столбцы. Я думаю, что это делает то, что вы хотите:

select Date, Group, ID, Name, 
       max(case when factorid = 1 then name end) as Factor1,
       max(case when factorid = 2 then name end) as Factor2,
       max(case when factorid = 3 then name end) as Factor3,
       max(case when factorid = 4 then name end) as Factor4
from t
group by Date, Group, ID, Name