更新时间:2022-12-11 22:51:06
如果您使用的是 SQL Server 2005+,那么您可以通过多种方式应用 PIVOT
函数.
If you are using SQL Server 2005+, then there are several ways that you can apply the PIVOT
function.
您可以以静态枢轴的形式对值进行硬编码:
You can hard-code the values in the form of a static pivot:
select *
from
(
select sname,
'hour_no_'+hr+'_'+convert(nvarchar(10), dt, 120) dt,
att
from stud
) st
pivot
(
max(att)
for dt in ([hour_no_1_2013-01-21], [hour_no_2_2013-01-21],
[hour_no_1_2013-01-22], [hour_no_2_2013-01-22])
) piv
或者您可以使用动态 sql 在运行时生成 sql 语句.查询的动态版本是:
Or you can use dynamic sql to generate the sql statement at run-time. The dynamic version of the query is:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols
= STUFF((SELECT ', ' + QUOTENAME('Hour_No_'+hr+'_'++convert(nvarchar(10), dt, 120))
from stud
group by hr, dt
order by dt, hr
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT sname,' + @cols + ' from
(
select sname,
''hour_no_''+hr+''_''+convert(nvarchar(10), dt, 120) dt,
att
from stud
) x
pivot
(
max(att)
for dt in (' + @cols + ')
) p '
execute(@query)
两者都给出结果:
| SNAME | HOUR_NO_1_2013-01-21 | HOUR_NO_2_2013-01-21 | HOUR_NO_1_2013-01-22 | HOUR_NO_2_2013-01-22 |
-----------------------------------------------------------------------------------------------------
| Abi | a | p | ml | od |
| bala | p | a | od | ml |