如何创建数据透视表 [英] how to create Pivot table

问题描述

my（access_event_logs）表

my (access_event_logs) table

[USERID]    [TIMESTAMPS]             [EVENTID]

1  019      2014-03-06 07:50:48.000   IN 
2  019      2014-03-06 17:02:39.000   OUT
3  019      2014-03-09 07:43:37.000   IN  
4  019      2014-03-09 14:34:59.000   OUT 
5  019      2014-03-10 07:43:34.000   IN 
6  019      2014-03-10 14:30:24.000   OUT

i想要透视表这一栏



1）用户ID

2）日期
3）在

4）out

5）按日期计算的总值（进出）

i want pivot table this columns

1) userid
2) date
3) in
4) out
5) total value (in and out) by date

推荐答案

请看看我对这个问题的评论。



试一试：

Please, see my comment to the question.

Try it:

SELECT userid, [IN], [OUT]
FROM (
    SELECT *
    FROM access_event_logs
    ) AS DT
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT

如果 [总计] 表示小时数的差异，请尝试：

If [total] means the difference in hours, try it:

SELECT userid, [IN], [OUT], DATEDIFF(hh,[IN], [OUT]) AS TotalHrs
FROM (
    SELECT userid, [IN], [OUT]
    FROM (
        SELECT *
        FROM access_event_logs
        ) AS DT
    PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
) AS FT

请参阅： DATEDIFF（sql） [ ^ ]







另一种解决方案：

See: DATEDIFF (sql)[^]



Another solution:

CREATE TABLE #tmp (ID INT IDENTITY(1,1), [USERID] VARCHAR(10), [TIMESTAMPS] DATETIME, [EVENTID] VARCHAR(10))
 
INSERT INTO #tmp ([USERID], [TIMESTAMPS], [EVENTID])
VALUES('019', '2014-03-06 07:50:48.000', 'IN'),
('019', '2014-03-06 17:02:39.000', 'OUT'),
('019', '2014-03-09 07:43:37.000', 'IN'),
('019', '2014-03-09 14:34:59.000', 'OUT'),
('019', '2014-03-10 07:43:34.000', 'IN'),
('019', '2014-03-10 14:30:24.000', 'OUT'),
('020', '2014-03-10 07:40:14.000', 'IN'),
('020', '2014-03-10 14:31:28.000', 'OUT'),
('021', '2014-03-10 07:45:39.000', 'IN')
 
SELECT t1.RowNo, t1.[USERID], t1.CurrDate, t1.[IN], t2.[OUT], DATEDIFF(hh, t1.[IN], t2.[OUT]) AS WorkHrs
FROM (
    SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [IN]
    FROM #tmp
    WHERE [EVENTID] = 'IN'
    ) AS t1 INNER JOIN (
        SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [OUT]
        FROM #tmp
        WHERE [EVENTID] = 'OUT'
        ) AS t2 ON t1.RowNo = t2.RowNo AND t1.USERID  = t2.USERID 
 
DROP TABLE #tmp

[/ EDIT]

[/EDIT]

这篇关于如何创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！