CODEWITHSUNDEEP
sql-server
Boom
Boom

Reputation: 29

Multiple Rows to Single Row

How can I retrieve a result if given multiple row data

Show multiple row data

The result base on performed column. The condition start with tserv_id ‘DISCHARGE’ and end with ‘ONPOWER’.

The conditions:

  1. DISCHARGE to ONPOWER (1 row)
  2. Any performed between last 1) ONPOWER-------------- ONPOWER (1 row)
  3. Any performed between last 2) ONPOWER-------------- ONPOWER (1 row)
  4. Any performed between last 3) ONPOWER ---------------ONPOWEWR (1 row)

Output

Code:

select 
    min(Case tserv_id when 'Discharge' then PERFORMED end) as tserv_discharge,
    min(Case tserv_id when 'ONPOWER' then PERFORMED end) as tserv_onpower  --How to retrieve data performed between disharge & first onpower?
from 
    ODS_REEFER
where 
    EQ_NBR = 'A1234';

select 
    min(Case tserv_id when 'ONPOWER' then PERFORMED end) as tserv_onpower,
    max(Case tserv_id when 'ONPOWER' then PERFORMED end) as tserv_onpower  --How to exclude performed first ONPOWER and how to read performed between second ONPOWER?
from 
    ODS_REEFER
where 
    EQ_NBR = 'A1234';

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use aggregation:

select min(Case tserv_id when 'Discharge' then PERFORMED end) as tserv_discharge,
       min(Case tserv_id when 'ONPOWER' then PERFORMED end) as tserv_onpower,
       min(Case tserv_id when 'YARD2TRUCK' then PERFORMED end) as tserv_yard2truck,
       min(Case tserv_id when 'FULL OUT' then PERFORMED end) as tserv_fullout,
       min(Case tserv_id when 'FULL IN' then PERFORMED end) as tserv_fullin
from ODS_REEFER
where EQ_NBR = 'EGSU5014174';

Upvotes: 2

Related Questions