SQL将行转置为列而不进行聚合 [英] SQL Transpose rows to columns without aggregate

问题描述

我知道答案很多，但没有用. 我的桌子看起来像

I know there is lot of answers but none of my use. My table looked like

我们在给定时间存储仪表的开和关状态.在每个关闭状态之后，都有一个打开状态.我们要像这样转置表格

where we store meter on and off status on given time. After every Off status there is an On status. We want to transpose table like this

    MeterNo |       [power off]       |          [power on]
____________________________________________________________________
  x0039938  | 2016-10-08 14:14:37.610 | 2016-10-08 14:17:15.047
____________________________________________________________________
  x0039938  | 2016-10-08 14:20:50.257 | NULL
____________________________________________________________________
  x0039938  | 2016-10-08 14:23:07.610 | 2016-10-08 14:23:17.920
____________________________________________________________________
  x0039940  | 2016-10-08 15:45:38.250 | 2016-10-08 15:52:40.080
____________________________________________________________________

以此类推

我尝试了此查询

    SELECT *
  FROM
(
  SELECT MeterNo, Status, CreatedOn
    FROM [BreakdownShutdowDetails]
) s
PIVOT
(
  MAX(CreatedOn) FOR Status IN ( [power off], [power on])
) p

仅给出每米的结果

获取每个仪表从关闭到打开状态之间的持续时间的基本想法. 请提出正确的查询建议. 谢谢.

Basic idea to get time duration between Off on On status of each meter. Please suggest for right query. Thanks.

更新1 样本数据

1   x0039938    power off   2016-10-08 14:14:37.610
2   x0039938    power on    2016-10-08 14:17:15.047
3   x0039938    power off   2016-10-08 14:20:50.257
4   x0039938    power off   2016-10-08 14:23:07.610
5   x0039938    power on    2016-10-08 14:23:17.920
6   x0039940    power off   2016-10-08 15:45:38.250
7   x0039940    power on    2016-10-08 15:52:40.080
8   x0040281    power off   2016-10-08 15:59:26.513
9   x0040281    power on    2016-10-08 16:20:23.323
10  x0039940    power off   2016-10-08 16:26:29.133
19  x0040281    power off   2016-10-08 17:17:48.900
22  x0039937    power off   2016-10-08 17:24:24.617
23  x0039937    power on    2016-10-08 17:24:38.590
24  x0039937    power off   2016-10-08 17:33:31.843
25  x0039937    power on    2016-10-08 17:35:47.470
27  x0039940    power off   2016-10-08 17:37:18.360
28  x0040281    power on    2016-10-08 17:40:08.093
30  x0043637    power off   2016-10-09 14:32:23.130
31  x0039937    power off   2016-10-09 14:32:24.893
32  x0040281    power off   2016-10-09 14:32:27.387
33  x0039940    power off   2016-10-09 14:32:29.407
34  x0040281    power on    2016-10-09 15:01:42.480

推荐答案

它通常称为差距和岛屿问题.尝试这样的事情(未经测试)

It called commonly called as Gaps and islands problem. Try something like this (Not tested)

SELECT MeterNo,
       [power off] = Min(CASE
                           WHEN status = 'power off' THEN CreatedOn
                         END),
       [power on] = Min(CASE
                           WHEN status = 'power on' THEN CreatedOn
                         END)
FROM   (SELECT *,
                Row_number() OVER (partition BY MeterNo ORDER BY CreatedOn) - 
                Dense_rank() OVER (partition BY MeterNo ORDER BY status)  AS seq_grp
        FROM   Yourtable) t
GROUP  BY MeterNo,
          seq_grp

这篇关于SQL将行转置为列而不进行聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！