Самый простой способ получить результат — использовать агрегатную функцию вместе с выражением CASE:
select
periodid,
periodname,
max(case when productname = 'A' then productid end) A_ID,
max(case when productname = 'A' then Productvalue end) A,
max(case when productname = 'B' then productid end) B_ID,
max(case when productname = 'B' then Productvalue end) B,
max(case when productname = 'C' then productid end) C_ID,
max(case when productname = 'C' then Productvalue end) C
from yourtable
group by periodid, periodname
order by periodid;
См. SQL Fiddle с демонстрацией.
Однако, если вы хотите использовать функцию PIVOT для получения результата, я бы сначала развернул столбцы productid
и productvalue
, чтобы у вас больше не было нескольких столбцов, а было несколько строк этих данных. Есть несколько способов развернуть данные: вы можете использовать функцию разворота или использовать ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ с ОБЪЕДИНЕНИЕМ ВСЕХ или ЗНАЧЕНИЙ (если вы используете SQL Server 2008+). Поскольку вы используете SQL Server 2008 R2, вот как вы можете использовать CROSS APPLY со VALUES, чтобы развернуть несколько столбцов:
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
('_ID', ProductID),
('', Productvalue)
) c (col, value)
См. SQL Fiddle с демонстрацией. Это преобразует ваши данные в формат:
| PERIODID | PERIODNAME | COL | VALUE |
|----------|------------|------|-------|
| 10 | Jan | A_ID | 100 |
| 10 | Jan | A | 15 |
| 20 | Feb | A_ID | 100 |
| 20 | Feb | A | 25 |
| 30 | Mar | A_ID | 100 |
Теперь вы можете легко применить функцию PIVOT к этим данным:
select periodid, periodname,
A_ID, A, B_ID, B, C_ID, C
from
(
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
('_ID', ProductID),
('', Productvalue)
) c (col, value)
) d
pivot
(
max(value)
for col in (A_ID, A, B_ID, B, C_ID, C)
) piv
order by periodid;
См. SQL Fiddle с демонстрацией.
Наконец, если у вас есть неизвестное количество названий продуктов, вы можете использовать динамический SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(productname+col)
from yourtable
cross apply
(
select '_ID', 1 union all
select '', 2
) c (col, so)
group by col, so, productname
order by productname, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT periodid, periodname,' + @cols + '
from
(
select periodid, periodname,
col = productname+col,
value
from yourtable
cross apply
(
values
(''_ID'', ProductID),
('''', Productvalue)
) c (col, value)
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p
order by periodid'
execute sp_executesql @query;
См. SQL Fiddle с демонстрацией. Все версии дают результат:
| PERIODID | PERIODNAME | A_ID | A | B_ID | B | C_ID | C |
|----------|------------|------|----|------|----|------|----|
| 10 | Jan | 100 | 15 | 200 | 12 | 300 | 22 |
| 20 | Feb | 100 | 25 | 200 | 14 | 300 | 23 |
| 30 | Mar | 100 | 35 | 200 | 18 | 300 | 38 |
person
Taryn
schedule
30.11.2013