如何在SQL Server中将行转置为列? [英] How to transpose rows into columns in SQL Server?

问题描述

我一直在研究如何转置或旋转这张桌子，但是工作了几个小时之后，我仍然坚持不懈，请您能帮我吗?

I have been working on how to transpose or pivot this table but after working for so many hours I am still stuck on this, can you please help me?

我的桌子看起来像这样:

My table looks like this:

CREATE TABLE Table1
(
     `Time` datetime, 
     `IN` int, 
     `OUT` int
);

INSERT INTO Table1 (`Time`, `IN`, `OUT`)
VALUES ('2017-04-05 15:53:00', 40, '35'),
       ('2017-04-05 15:24:00', 40, '35'),
       ('2017-04-05 15:23:00', 40, '35'),
       ('2017-04-05 14:22:00', 42, '40'),
       ('2017-04-05 14:21:00', 42, '40'),
       ('2017-04-05 14:20:00', 42, '40'),
       ('2017-04-05 13:19:00', 33, '30'),
       ('2017-04-05 13:18:00', 33, '30'),
       ('2017-04-05 13:17:00', 33, '30'),
       ('2017-04-05 13:16:00', 33, '30'),
       ('2017-04-05 13:15:00', 33, '30'),
       ('2017-04-05 12:14:00', 29, '25'),
       ('2017-04-05 12:13:00', 29, '25'),
       ('2017-04-05 12:12:00', 29, '25'),
       ('2017-04-05 12:11:00', 29, '25'),
       ('2017-04-05 11:14:00', 35, '33'),
       ('2017-04-05 11:13:00', 35, '33'),
       ('2017-04-05 11:12:00', 35, '33'),
       ('2017-04-05 11:11:00', 35, '33');

我希望我的输出类似于此

I want my output to be something similar to this

在此处输入图片描述

即使输入了几次，"IN"列中的值也始终是相同的，因此我只需要在表中输入1个值即可，与"OUT"列中的值相同.

The value in 'IN' column is always the same for that particular hour even entered several time, so I just need 1 value to be in my table same as with the value in 'OUT' column

Accum是每小时的累计总和，只要有可能，就将其设为可选.

The Accum is the accumulated sum for each hour, just make it optional if it is even possible.

我正在做的第一部分是用此查询转置2列

I am doing the first part which is to transpose the 2 columns with this query

SELECT 
    'IN' AS A, [2017-04-05 15:53:00], [2017-04-05 14:22:00],,,,
FROM 
    (SELECT 
         [Time], [in], [out] 
     FROM 
         Table1) AS SourceTable
PIVOT
    (MAX([IN])
        FOR [time] IN ([2017-04-05 15:53:00], [2017-04-05 14:22:00],,,,)) AS PivotTable;

推荐答案

您可以尝试一下，

select [Type], [11], [12], [13], [14], [15]
from
(
  Select [Time], [Type], value
  from (
    select t2.*, sum([OUT]) over (order by [TIME]) as ACCUM
    from (Select Distinct DATEPART(HOUR, Time) as [Time], [IN], [OUT] from table1) t2
    ) A
  unpivot
  (
    value for [TYPE] in ([IN],[OUT],[ACCUM])
  ) unpiv
) src
pivot
(
  sum(value)
  for [Time] in ([11], [12], [13], [14], [15])
) piv

从此处获取更多详细信息以使其动态化， 在Sql中转置列和行的简单方法吗? 累积汇总列

Get more details from here to make it dynamic, Simple way to transpose columns and rows in Sql? Accumulate a summarized column

这篇关于如何在SQL Server中将行转置为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！