Merging Multiple Rows into a single Row (SQL)

Question

I am working on a project to create SLAs for different orders. To do this, I need to merge multiple rows into a single row, although there are times it will be on more than 1 row. Here is how the data is currently set up:

OrderNumber | Stage1 | Stage2 | Stage3 | Stage4 |
=================================================
1           |1/1/2016|*NULL*  |*NULL*  | *NULL*
1           |*NULL*  |2/1/2016|*NULL*  | *NULL*
1           |*NULL*  |*NULL*  |3/1/2016| *NULL*
1           |*NULL*  |*NULL*  |*NULL*  | 4/1/2016
1           |*NULL*  |5/1/2016|*NULL*  | *NULL*

The way I am hoping to see the data is like this:

OrderNumber | Stage1 | Stage2 | Stage3 | Stage4 |
=================================================
1           |1/1/2016|2/1/2016|3/1/2016| 4/1/2016
1           |1/1/2016|5/1/2016|*NULL*  | *NULL*

OR

OrderNumber | Stage1 | Stage2 | Stage3 | Stage4 |
=================================================
1           |1/1/2016|2/1/2016|3/1/2016| 4/1/2016
1           |*NULL*  |5/1/2016|*NULL*  | *NULL*

There are two problems I see, 1) Merging multiple rows into a single row and 2) If a value shows up in a column more than once, displaying it in a new row (ideally with values from previous stages appearing in earlier columns, but this isn't required).

The closest solution I have found is this: Merge two rows in SQL , but it did not work for this problem.

Any help would be greatly appreciated.

Edit Updated Tags

what happen if Stage 1 has two not null dates instead of one? which one you will pair wiith stage2 two dates? — Juan Carlos Oropeza, Apr 05 '16 at 17:09
Possible duplicate of [Merge multiple rows into a single row](http://stackoverflow.com/questions/10553198/merge-multiple-rows-into-a-single-row) — Ken White, Apr 05 '16 at 17:10
Easiest approach I can think of is to assign a row number to each stage when the value isn't null. and then Join the data based on rownumber — xQbert, Apr 05 '16 at 17:46
Is it sql-server or mysql? sql-server means Microsoft SQL Server. Please use tags properly — FLICKER, Apr 05 '16 at 18:04
"what happen if Stage 1 has two not null dates instead of one? which one you will pair wiith stage2 two dates?" – Juan Carlos Oropeza In this scenario, The first date (earliest) would be on the first line, the second date (later date) would be on the second line for both stages. "Possible duplicate of Merge multiple rows into a single row" – Ken White I had looked into this one and it looked like the focus was on concatenation of the data, which won't work here. — Fowipple, Apr 05 '16 at 18:10
@Fowipple, It is like the Stage2, which has 2 not null date., Isn't it? — FLICKER, Apr 05 '16 at 18:10
I think it would be way better to correct this horrible table design than to write queries that must muddle through it. Just my 5c. — Thorsten Kettner, Apr 05 '16 at 18:24

score 0 · Accepted Answer · answered Apr 05 '16 at 18:27

I solve the problem using SQL Server 2012, using window functions. As MySQL also supports window function, it doesn't matter which database it is. If MySQL doesn't support CTE, you can replace it with derived table.

There are 4 Stages so we need 4 join.

;with cte as (
select t1.OrderNumber, t1.Stage1, t2.Stage2, t3.Stage3, t4.Stage4
, LAG(t1.Stage1) over (partition by t1.OrderNumber order by t1.Stage1) AS Prev1
, LAG(t2.Stage2) over (partition by t2.OrderNumber order by t2.Stage2) AS Prev2
, LAG(t3.Stage3) over (partition by t3.OrderNumber order by t3.Stage3) AS Prev3
, LAG(t4.Stage4) over (partition by t4.OrderNumber order by t4.Stage4) AS Prev4
from #t t1
  inner join #t t2 on t2.OrderNumber = t1.OrderNumber and t2.Stage2 is not null
  inner join #t t3 on t3.OrderNumber = t1.OrderNumber and t3.Stage3 is not null
  inner join #t t4 on t4.OrderNumber = t1.OrderNumber and t4.Stage4 is not null
where t1.Stage1 is not null
)
select OrderNumber
, IIF(Stage1 <> Prev1 or Prev1 is null, Stage1, Null) AS Stage1 
, IIF(Stage2 <> Prev2 or Prev2 is null, Stage2, Null) AS Stage2
, IIF(Stage3 <> Prev3 or Prev3 is null, Stage3, Null) AS Stage3
, IIF(Stage4 <> Prev4 or Prev4 is null, Stage4, Null) AS Stage4
from cte

Replace #t with your table name

Result:

+-------------+------------+------------+------------+------------+
| OrderNumber |   Stage1   |   Stage2   |   Stage3   |   Stage4   |
+-------------+------------+------------+------------+------------+
|           1 | 2016-01-01 | 2016-01-02 | 2016-01-03 | 2016-01-04 |
|           1 | NULL       | 2016-01-05 | NULL       | NULL       |
+-------------+------------+------------+------------+------------+

This looks like it will work perfectly. Thank you FLICKER! – Fowipple Apr 05 '16 at 18:29 — Fowipple, Apr 05 '16 at 18:29

score 0 · Answer 2 · answered Apr 05 '16 at 18:27

0

You can unpivot the data, then pivot again

SELECT OrderNumber, [Stage1], [Stage2], [Stage3], [Stage4] 
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Vals ORDER BY Val) Rn FROM 
    Table1 t
    UNPIVOT
    (
        Val
        FOR Vals IN ([Stage1], [Stage2], [Stage3], [Stage4])
    ) up
) t
PIVOT 
(
    MAX(Val)
    FOR Vals IN ([Stage1], [Stage2], [Stage3], [Stage4])
) p

answered Apr 05 '16 at 18:27

JamieD77

13,796
1
17
27

@Fowipple i really hope you check the execution plan before you just use any old sql you find online – JamieD77 Apr 05 '16 at 18:35
Yeah, I don't know what I was thinking @JamieD77. – shawnt00 Apr 05 '16 at 18:54
@shawnt00 lol.. i was trying to get yours working just so i could see how it would compare performance wise, but i couldn't figure it out. I figured it would be better than the selected answer at least. – JamieD77 Apr 05 '16 at 18:57
I've posted a tested query along the lines of my original attempt. I suppose you can test against it though I'll bet yours wins. – shawnt00 Apr 05 '16 at 19:11

shawnt00 · Answer 3 · 2016-04-05T19:18:01.570

with data as (
    select
        OrderNumber, Stage1, Stage2, Stage3, Stage4,
        row_number() over (
            partition by OrderNumber
            order by
                case when Stage1 is not null then 0 else 1 end, Stage1,
                case when Stage2 is not null then 0 else 1 end, Stage2,
                case when Stage3 is not null then 0 else 1 end, Stage3,
                case when Stage4 is not null then 0 else 1 end, Stage4) rn,
        row_number() over (
            partition by OrderNumber
            order by case when Stage1 is not null then 0 else 1 end, Stage1) r1,
        row_number() over (
            partition by OrderNumber
            order by case when Stage2 is not null then 0 else 1 end, Stage2) r2,
        row_number() over (
            partition by OrderNumber
            order by case when Stage3 is not null then 0 else 1 end, Stage3) r3,
        row_number() over (
            partition by OrderNumber
            order by case when Stage4 is not null then 0 else 1 end, Stage4) r4
    from T
)
select
    OrderNumber, s1.Stage1, s2.Stage2, s3.Stage3, s4.Stage4
from
    data d
    cross apply (select d1.Stage1 from data d1 where d1.r1 = d.rn) s1(Stage1)
    cross apply (select d2.Stage2 from data d2 where d2.r2 = d.rn) s2(Stage2)
    cross apply (select d3.Stage3 from data d3 where d3.r3 = d.rn) s3(Stage3)
    cross apply (select d4.Stage4 from data d4 where d4.r4 = d.rn) s4(Stage4)
where
    coalesce(s1.Stage1, s2.Stage2, s3.Stage3, s4.Stage4) is not null

@JamieD77, OP said SQL Server. – shawnt00 Apr 05 '16 at 18:37 — shawnt00, Apr 05 '16 at 18:37
did you try to run this on sql server? – JamieD77 Apr 05 '16 at 18:38 — JamieD77, Apr 05 '16 at 18:38

Merging Multiple Rows into a single Row (SQL)

3 Answers3