sql table pivot

Question

我正在尝试进行查询，返回整个月（30 天）每个员工的结果。数据如下：

employeeID day DayDutyDate   fromTime                   Period
1           26  2014-03-26  2000-01-01 09:30:00.000     Time1
1           26  2014-03-26  2000-01-01 14:00:00.000     Time2
1           27  2014-03-27  2000-01-01 09:30:00.000     Time1
1           27  2014-03-26  2000-01-01 14:00:00.000     Time2

我正在按以下方式调整日期：

PIVOT ( MIN(fromtime) FOR days IN ( [26],[27]) (I am being breif not listing all month days right now)

结果如下：

EmployeeID 26                       27
1          2000-01-01 09:30:00.000  null
1          2000-01-01 14:00:00.000  null
1          null                     2000-01-01 09:30:00.000  
1          null                     2000-01-01 14:00:00.000

我需要的是：

EmployeeID 26                       27
1          2000-01-01 09:30:00.000  2000-01-01 09:30:00.000
1          2000-01-01 14:00:00.000  2000-01-01 14:00:00.000

知道吗？

Answer 1

此查询：

SELECT employeeID, [26], [27], Period
FROM 
(SELECT employeeID, [day], fromtime, Period
FROM #MyTable) p
PIVOT
(
MIN(fromtime)
FOR [day] IN
( [26], [27])
) AS pvt

生成以下输出：

employeeID  26                      27                     Period
==================================================================
1           2000-01-01 09:30:00.000 2000-01-01 09:30:00.000 Time1
1           2000-01-01 14:00:00.000 2000-01-01 14:00:00.000 Time2

Answer 2

您还可以使用动态 SQL 来透视数据。请参阅以下示例：

DECLARE @cols as varchar(max)
DECLARE @sql as varchar(max)
SELECT @cols  = coalesce(@cols  + ',','') + '[' + day + ']' FROM #MyTable
SET @sql = 
         'SELECT employeeID, ' + @cols + ', Period
           FROM (
                 SELECT employeeID, [day], fromtime, Period
                 FROM #MyTable
                 ) as P
           PIVOT 
                 (
                 MIN(fromtime)FOR [day] IN (' + @cols + ')
                 )AS pvt'
EXEC(@sql)