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

I have to merge rows from table into one row. The table looks like below:

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

Table name: dbo.Operations

表名: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

I would like to select the result. Should I use JOINs? Can I create the query without GROUP BY statement?

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

4 个解决方案

#1

using cross apply() to get the next 'end' for each 'start':

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

#2

I would do it like this:

我会这样做:

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;

or with the more readable version: ;-)

或者更易读的版本:;-)

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

#3

Although answer is already accepted, below query gives the required output:

虽然答案已被接受,但以下查询提供了所需的输出:

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'

output

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

#4

Make the 1st and 2nd table in the same number of columns and same column name (alias name). And use the UNION on the both table select statement.

使第一个和第二个表具有相同的列数和相同的列名(别名)。并在两个表select语句中使用UNION。

#1