CODEWITHSUNDEEP
sqlsql-serverdatabaseleft-join
Mateusz
Mateusz

Reputation: 334

How to merge database table rows into one row

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

Table name: 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?

Upvotes: 4

Views: 723

Answers (4)

Vishnu Prakash
Vishnu Prakash

Reputation: 1

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.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

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

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

Upvotes: 3

Gaurav Rajput
Gaurav Rajput

Reputation: 647

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

Upvotes: 1

3barney21
3barney21

Reputation: 11

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

Upvotes: 1

Related Questions