如何将数据库表格行合并成一行

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

I expect the result:

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

我想选择结果。 我应该使用JOIN吗？我能否在没有GROUP BY语句的情况下创建查询？

- Mateusz

---

你希望得到的结果背后的逻辑是什么？ - ahmed abdelqader

你想要像结果一样进行 SELECT 操作，还是想要转换表的模式和数据，使表内容看起来像结果？ - mortb

只选择结果。 - Mateusz

Answer 1

使用cross apply()获取每个“start”对应的下一个“end”：

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';

- SqlZim

---

交叉应用（Cross Apply）对于像这样相当简单的事情来说似乎有点过重了，您觉得呢？ - Matthew

@Matthew 你应该只是编辑你的答案而不是删除它。它仍然是一个好答案。 - SqlZim

啊，唉，我真的没有时间，我在工作中只使用我的声望来获取我需要的答案。 - Matthew

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';

- 3barney21

---

不良习惯：使用旧式的JOIN - Aaron Bertrand - SqlZim

这并没有考虑到每个“number”的多个起始和结束实例，例如对于数字“A1”，返回4行而不是2行。 - SqlZim

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
----------------------------------------------------------------------

- Gaurav Rajput

Answer 4

将第一张表和第二张表设置为相同列数和相同列名称（别名）。并在两个表的SELECT语句上使用UNION。

- Vishnu Prakash