CODEWITHSUNDEEP
sqlsql-server
Diggs11
Diggs11

Reputation: 31

SQL Combine Columns

I have an issue where my SQL query will not get rid of null values.

I have tried a myriad of different techniques, but I am a novice.

My desired result is an Order number followed by the date which the status occurs all in one line. 

select mo.order_id OrderID,
case when osr.order_status_cd = 120 and osr.create_date is not null then    osr.create_date end as POCreated,
case when osr.order_status_cd = 220 and osr.create_date is not null then osr.create_date end as Ordered,
case when osr.order_status_cd = 300 and osr.create_date is not null then osr.create_date end as Shipped,
case when osr.order_status_cd = 400 and osr.create_date is not null then osr.create_date end as Received,
case when osr.order_status_cd = 500 and osr.create_date is not null then    osr.create_date end as Completed,
from order_status_record osr
inner join msorder mo on mo.order_id = osr.msorder_id

Instead of getting results that look like this:

OrderID POCreated   Ordered     Shipped      Received    Completed
497822  11/18/2012  NULL          NULL        NULL        NULL
497822  NULL       11/19/2012     NULL        NULL        NULL
497822  NULL        NULL       11/19/2012     NULL        NULL
497822  NULL        NULL          NULL     11/19/2012     NULL
497822  NULL        NULL          NULL        NULL     11/19/2012

I want this:

OrderID POCreated   Ordered     Shipped      Received    Completed
497822  11/18/2012  11/19/2012  11/19/2012   11/19/2012  11/19/2012

Do I need to create a virtual table? Do I need an if function? Why do the Nulls still populate?

Any help would be much appreciated.

Thanks,

Andrew

Upvotes: 2

Views: 169

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Assuming that (msorder_id, order_status_cd) is UNIQUE in table order_status_record, here is a solution without GROUP BY:

SELECT 
    mo.order_id         AS OrderID,
    osr120.create_date  AS POCreated,
    osr220.create_date  AS Ordered,
    osr300.create_date  AS Shipped,
    osr400.create_date  AS Received,
    osr500.create_date  AS Completed
FROM 
    msorder AS mo 
  LEFT JOIN 
    order_status_record AS osr120 
      ON  osr120.msorder_id = mo.order_id 
      AND osr120.order_status_cd = 120 
  LEFT JOIN 
    order_status_record AS osr220 
      ON  osr220.msorder_id = mo.order_id 
      AND osr220.order_status_cd = 220 
  LEFT JOIN 
    order_status_record AS osr300 
      ON  osr300.msorder_id = mo.order_id 
      AND osr300.order_status_cd = 300 
  LEFT JOIN 
    order_status_record AS osr400 
      ON  osr400.msorder_id = mo.order_id 
      AND osr400.order_status_cd = 400 
  LEFT JOIN 
    order_status_record AS osr500 
      ON  osr500.msorder_id = mo.order_id 
      AND osr500.order_status_cd = 500 ;

Upvotes: 2

McGarnagle
McGarnagle

Reputation: 102743

Use GROUP BY to group the order IDs into a row, and MAX on the case statements to get the right row:

select mo.order_id OrderID,
    MAX(case when osr.order_status_cd = 120 and osr.create_date is not null then    osr.create_date end) as POCreated,
    MAX(case when osr.order_status_cd = 220 and osr.create_date is not null then osr.create_date end) as Ordered,
    MAX(case when osr.order_status_cd = 300 and osr.create_date is not null then osr.create_date end) as Shipped,
    MAX(case when osr.order_status_cd = 400 and osr.create_date is not null then osr.create_date end) as Received,
    MAX(case when osr.order_status_cd = 500 and osr.create_date is not null then    osr.create_date end) as Completed,
from order_status_record osr
inner join msorder mo on mo.order_id = osr.msorder_id
GROUP BY order_id

Upvotes: 2

Taryn
Taryn

Reputation: 247650

If you add an aggregate function to each of the CASE statements and then a GROUP BY the records will consolidate into a single row:

select mo.order_id OrderID,
  max(case when osr.order_status_cd = 120 and osr.create_date is not null then osr.create_date end) as POCreated,
  max(case when osr.order_status_cd = 220 and osr.create_date is not null then osr.create_date end) as Ordered,
  max(case when osr.order_status_cd = 300 and osr.create_date is not null then osr.create_date end) as Shipped,
  max(case when osr.order_status_cd = 400 and osr.create_date is not null then osr.create_date end) as Received,
  max(case when osr.order_status_cd = 500 and osr.create_date is not null then osr.create_date end) as Completed,
from order_status_record osr
inner join msorder mo 
  on mo.order_id = osr.msorder_id
group by mo.order_id

Upvotes: 3

Related Questions