如何将数据库表行合并为一行

Question

我必须将表中的行合并为一行。该表如下所示：

表名: dbo.Operations

NUMBER  |   OPERATION_DATE              |   STATUS      | WEIGHT_BEFORE | WEIGHT_AFTER
A1      |   2016-11-10 23:18:59.000     |   START       |   3077        |   3077
A1      |   2016-11-10 23:47:59.000     |   END         |   3077        |   2741
A1      |   2016-11-10 23:48:59.000     |   START       |   2741        |   2741
A1      |   2016-11-10 23:50:59.000     |   END         |   2741        |   2510
B3      |   2016-11-10 23:18:59.000     |   START       |   300         |   300
B3      |   2016-11-10 23:47:59.000     |   END         |   290         |   287

我希望结果是：

NUMBER  |   START_DATE                  |   END_DATE                    | WEIGHT_BEFORE | WEIGHT_AFTER
A1      |   2016-11-10 23:18:59.000     |   2016-11-10 23:47:59.000     |   3077        |   2741
A1      |   2016-11-10 23:48:59.000     |   2016-11-10 23:50:59.000     |   2741        |   2510
B3      |   2016-11-10 23:18:59.000     |   2016-11-10 23:47:59.000     |   300         |   287

我想选择结果。我应该使用联接吗？我可以在不使用GROUP语句的情况下创建查询吗？

Answer 1

使用cross apply()为每个“开始”获取下一个“结束”：

select 
    t.Number
  , Start_Date = t.Operation_Date
  , End_date = x.Operation_Date
  , t.Weight_Before
  , x.Weight_After
from dbo.Operations t
  cross apply (
    select top 1 i.Operation_Date, i.Weight_After
    from dbo.Operations i
    where i.Number = t.Number
      and i.Status = 'End'
      and i.Operation_Date > t.Operation_Date
    order by i.Operation_Date asc
    ) x
where t.Status = 'start';

Answer 2

我会这样做：

SELECT a.number
    ,a.operation_date AS START_DATE
    ,b.operation_date AS END_DATE
    ,a.weight_before
    ,b.weight_after
FROM dbo.Operations a
    ,dbo.Operations b
WHERE a.STATUS = 'START'
    AND b.STATUS = 'END'
    AND a.number = b.number;

或使用更具可读性的版本：;-)

SELECT a.number
    ,a.operation_date AS START_DATE
    ,b.operation_date AS END_DATE
    ,a.weight_before
    ,b.weight_after
FROM dbo.Operations a
JOIN dbo.Operations b ON a.number = b.number
WHERE a.STATUS = 'START'
    AND b.STATUS = 'END';

Answer 3

尽管答案已经被接受，但下面的查询提供了所需的输出：

DECLARE @SAMPLEDATA TABLE(NUMBER  VARCHAR(10),   OPERATION_DATE DATETIME,       STATUS   VARCHAR(20), WEIGHT_BEFORE INT, WEIGHT_AFTER INT)

INSERT INTO @SAMPLEDATA VALUES
('A1',        '2016-11-10 23:18:59.000'     ,   'START'       ,   3077        ,   3077),
('A1',         '2016-11-10 23:47:59.000'     ,   'END'        ,   3077        ,   2741),
('A1',         '2016-11-10 23:48:59.000'     ,   'START'       ,   2741        ,   2741),
('A1',         '2016-11-10 23:50:59.000'     ,   'END'         ,   2741        ,   2510),
('B3',         '2016-11-10 23:18:59.000'     ,   'START'       ,   300         ,   300),
('B3',         '2016-11-10 23:47:59.000'     ,   'END'         ,   290         ,   287)

;WITH CTE
AS
(
    SELECT SNO,NUMBER,OPERATION_DATE,WEIGHT_BEFORE,WEIGHT_AFTER,STATUS FROM     (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 100))SNO,* FROM @SAMPLEDATA)A
)
SELECT NUMBER,OPERATION_DATE [START_DATE],
(SELECT OPERATION_DATE FROM CTE T2 WHERE T2.SNO=T1.SNO+1)END_DATE,
WEIGHT_BEFORE,
(SELECT WEIGHT_AFTER FROM CTE T2 WHERE T2.SNO=T1.SNO+1)WEIGHT_AFTER
 FROM CTE T1 WHERE STATUS='START'

输出

----------------------------------------------------------------------
--NUMBER    START_DATE  END_DATE    WEIGHT_BEFORE   WEIGHT_AFTER
----------------------------------------------------------------------
A1  2016-11-10 23:18:59.000 2016-11-10 23:47:59.000 3077    2741
A1  2016-11-10 23:48:59.000 2016-11-10 23:50:59.000 2741    2510
B3  2016-11-10 23:18:59.000 2016-11-10 23:47:59.000 300     287
----------------------------------------------------------------------