sql-server - 使用一个 sql 语句在数据透视表中创建多于一列

Question

我需要对相同项目编号的数据透视表项目数据中的日期进行排序

项目看起来像这样:

"Project nr"   "Task"                "Task deadline"  "Task Type Production"
123             pack                  1 april 2013    Pack
123             Leave production      3 april 2013    Leave Production
123             Flight date           9 april 2013    Flight Date

制作“任务类型制作”是为了保证字段内容始终一致我只能在数据透视表中创建一列。有没有办法在 3 列中显示这些信息它看起来像这样:

Project nr ;  Pack   ;  leave production ; flightdate   


SELECT  [TaskDeadline] AS Packed
FROM         MSP_EpmTask_UserView where  [Task Type Production] = 'Packed'
SELECT  [TaskDeadline] AS LeaveProduction
FROM         MSP_EpmTask_UserView where  [Task Type Production] =  'Leave Production' 
SELECT  [TaskDeadline] AS FlightDate
FROM         MSP_EpmTask_UserView where  [Task Type Production] =  'Flight Date'

谢谢安妮

Answer 1

这可以使用聚合函数和 CASE 表达式轻松完成:

select  [Project nr],
    MAX(case when [Task Type Production] = 'Pack' then [Task deadline] end) as Pack,
    MAX(case when [Task Type Production] = 'Leave Production' then [Task deadline] end) as [Leave Production],
    MAX(case when [Task Type Production] = 'Flight Date' then [Task deadline] end) as [Flight Date]
from MSP_EpmTask_UserView 
group by    [Project nr]

参见 SQL Fiddle with Demo

如果你想在 SQL Server 中使用 PIVOT 函数，那么查询将是:

select *
from
(
  select [Project nr],[Task deadline], [Task Type Production]
  from MSP_EpmTask_UserView 
) src
pivot
(
  max([Task deadline])
  for [Task Type Production] in ([Pack], [Leave Production],
                                 [Flight Date])
) piv

参见 SQL Fiddle with Demo .

最后，这可以通过对您的表使用多个连接来完成:

select t1.[Project nr],
  t1.[Task deadline] pack,
  t2.[Task deadline] [Leave Production],
  t3.[Task deadline] [Flight Date]
from MSP_EpmTask_UserView t1
left join MSP_EpmTask_UserView t2
  on t1.[Project nr] = t2.[Project nr]
  and t2.[Task Type Production] = 'Leave Production'
left join MSP_EpmTask_UserView  t3
  on t1.[Project nr] = t3.[Project nr]
  and t3.[Task Type Production] = 'Flight Date'
where t1.[Task Type Production] = 'Pack'

参见 SQL Fiddle with Demo

所有查询的结果是:

| PROJECT NR |       PACK | LEAVE PRODUCTION | FLIGHT DATE |
------------------------------------------------------------
|        123 | 2013-04-01 |       2013-04-03 |  2013-04-09 |