CODEWITHSUNDEEP
plsqlleft-joinflatten
Mindbender
Mindbender

Reputation: 49

how to return results on same line when different values

I have a SQL query that returns the following:

ID  SUBID     Volume   Gross    Net
89      D     0        10039    10000
89      X     10039 
89      D     0        6991     7001
89      X     6991

The volume value from SUBID X comes from a query against one table and the Gross and Net values for SUBID D come from a left join to another table.

The IDs are the same and it is data for the same ID, just categorized differently (D versus X) in the 2 tables.

How do I get the result to look like this?

ID  SUBID     Volume   Gross    Net
89      X     10039    10039    10000
89      X     6991     6991     7001

Upvotes: 0

Views: 45

Answers (1)

d r
d r

Reputation: 7846

With your data provided ...

WITH       --  S a m p l e     D a t a : 
    your_data AS
        ( Select 89 as ID, 'D' as SUBID, 0 as Volume, 10039 as Gross, 10000 as Net UNION ALL
          Select 89,  'X',  10039, Null, Null UNION ALL
          Select 89,  'D',      0, 6991, 7001 UNION ALL
          Select 89,  'X',   6991, Null, Null
        )

... one option could be to join 'D' and 'X' rows like below ...

--    S Q L : 
Select     x.ID, x.SUBID, d.SUBID as D_SUBID, x.VOLUME, 
           Coalesce(x.GROSS, d.GROSS) as GROSS,
           Coalesce(x.NET, d.NET) as NET
From       (   Select     *
               From       your_data
               Where      SUBID = 'X'
           ) x
Inner Join ( Select     *
             From       your_data
           ) d ON( d.ID = x.ID And 
                   REPLACE(d.SUBID, 'D', 'X') = x.SUBID And 
                   x.VOLUME = d.GROSS)
Order By  x.VOLUME Desc

R e s u l t :

ID SUBID D_SUBID VOLUME GROSS NET
89 X D 10039 10039 10000
89 X D 6991 6991 7001

fiddle

NOTE:
This uses VOLUME = GROSS as a join condition. There could be a problem with the possibility of having multiple rows with same volume/gross combo which would result with partial cross linkings.

Upvotes: 0

Related Questions