为了得到你想要的结果，你将不得不取消表格中各列的透视，并应用透视函数。
解除透视可以使用UNPIVOT 函数，也可以使用CROSS APPLY 与VALUES 。
UNPIVOT。
select rn,
col +'_'+cast(dr as varchar(10)) col,
new_values
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
unpivot
new_values
for col in (name, date, value)
) un;
CROSS APPLY:
select rn,
col +'_'+cast(dr as varchar(10)) col,
c.value
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
cross apply
values
('Name', name), ('Date', date), ('Value', Value)
) c (col, value);
参见SQL Fiddle与两个版本的演示。 这样就得到了结果。
| RN | COL | NEW_VALUES |
-----------------------------
| 1 | name_1 | Foo |
| 1 | date_1 | 04/05/2011 |
| 1 | value_1 | 15 |
| 2 | name_1 | Foo |
| 2 | date_1 | 04/06/2011 |
| 2 | value_1 | 321 |
| 1 | name_2 | My |
| 1 | date_2 | 05/15/2005 |
| 1 | value_2 | 36 |
这些查询采取你现有的列值，并将它们转换为行。一旦它们变成了行，你就通过使用窗口函数来创建新的列名 dense_rank.
一旦数据被转换为行，你就可以使用新的列名（用dense_rank 的值创建）并应用PIVOT 函数。
PIVOT与UNPIVOT。
select name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3
select rn,
col +'_'+cast(dr as varchar(10)) col,
new_values
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
unpivot
new_values
for col in (name, date, value)
) src
pivot
max(new_values)
for col in (name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3)
) piv;
带CROSS APPLY的PIVOT。
select name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3
select rn,
col +'_'+cast(dr as varchar(10)) col,
c.value
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
cross apply
values
('Name', name), ('Date', date), ('Value', Value)
) c (col, value)
) src
pivot
max(value)
for col in (name_1, date_1, value_1,
name_2, date_2, value_2,
name_3, date_3, value_3)
) piv;
Dyanmic PIVOT:
如果你有有限的或已知数量的列，上述版本将非常有效，如果没有，你将需要使用动态SQL。
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col +'_'+cast(dr as varchar(10)))
select dense_rank() over(order by name) dr
from yourtable
cross apply
values(1, 'Name'), (2, 'Date'), (3, 'Value')
) c (sort, col)
group by col, dr, sort
order by dr, sort
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ' + @cols + '
select rn,
col +''_''+cast(dr as varchar(10)) col,
c.value
select name,
convert(varchar(10), date, 101) date,
cast(value as varchar(10)) value,
dense_rank() over(order by name) dr,
row_number() over(partition by name order by date) rn
from yourtable
cross apply
values
(''Name'', name), (''Date'', date), (''Value'', Value)
) c (col, value)
pivot
max(value)
for col in (' + @cols + ')
execute(@query)
每个查询的结果是。
| NAME_1 | DATE_1 | VALUE_1 | NAME_2 | DATE_2 | VALUE_2 | NAME_3 | DATE_3 | VALUE_3 |
-------------------------------------------------------------------------------------------------
| Foo | 04/05/2011 | 15 | My | 05/15/2005 | 36 | Test | 01/01/2001 | 10 |
| Foo | 04/06/2011 | 321 | My | 07/25/2005 | 75 | Test | 01/02/2001 | 17 |
| (null) | (null) | (null) | (null) | (null) | (null) | Test | 01/03/2001 | 52 |
