SQL Server 行值作为列名数据透视表?

2022-01-22 00:00:00 pivot sql-server-2008 sql-server

我在 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，低于该日期为该员工在该特定日期的Hours_Worked"值.

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 查看快照

推荐答案

您可以使用 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 Fiddle with Demo

如果你有未知数量的值，那么你可以使用动态 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 Fiddle with Demo

相关文章