SQL Server行值作为列名数据透视表? [英] SQL Server row values as column names Pivot Table?
问题描述
我在SQL Server 2008中具有以下视图.
I have the following view in SQL Server 2008.
DEPT | EMP_ID | EMP_NAME | P_DATE | HOURS_WORKED
我希望视图是这样的:
DEPT | EMP_ID | EMP_NAME | 2012-09-28 | 2012-09-29 | 2012-09-30 | 2012-10-01 ...
其中上述日期列标题为P_DATE,其下方为该雇员在该特定日期的小时工作"值.
where the above date column header is P_DATE below which is "Hours_Worked" values of that employee on that particular date.
喜欢
2012-09-28
09:00:00
10:00:00
我不确定是否可以使用Pivot实现它.
I am not sure whether I could achieve it using Pivot.
请转到此链接以清楚了解:SQL Server视图快照
Please go to this link for clear understanding : SQL Server View Snapshots
推荐答案
您可以使用PIVOT
函数执行此操作.如果您知道要转换为列的值而不是硬编码,那么可以使用静态枢轴:
You can perform this with the PIVOT
function. If you know the values that you want to turn into columns than you can hard code then using a static pivot:
select *
from
(
select dept, emp_id, emp_name, p_date, hours_worked
from table1
) x
pivot
(
max(hours_worked)
for p_date in ([2012-10-19], [2012-10-20], [2012-10-21])
) p
请参见带有演示的SQL提琴
如果值的数量未知,则可以使用动态sql来PIVOT
数据:
If you have an unknown number of values, then you can use dynamic sql to PIVOT
the data:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ','
+ QUOTENAME(convert(char(10), p_date, 120))
from table1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT dept, emp_id, emp_name,' + @cols + ' from
(
select dept, emp_id, emp_name, p_date, hours_worked
from table1
) x
pivot
(
max(hours_worked)
for p_date in (' + @cols + ')
) p '
execute(@query)
请参见带演示的SQL提琴
这篇关于SQL Server行值作为列名数据透视表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!