grouping db2 results of multiple rows to give columns for the different results

Question

I have a fairly simple query that executes correctly in DB2 but I'm having troubles figuring out how to group results to give me 2 columns with values that replace the colum of 2 different rows. In other words, for each product I get 2 rows (one with active price, one with temporary price) but I want to make it so that I get one distinct product row with a column for each price type and the price

The query:

    select distinct grouping, body, fabric,color,thread,detail, category,p.priceType,p.price
    from ordering offs
    inner join pricing p
    on offs.body = p.bodyp
    where priceType in ('Active','Temporary')
    and offs.category in ('A','B','C');

What I'm getting:

    grouping  |  body  |  fabric  |  color  |  thread  |  detail  |  category  |  p.priceType  |  price
    -----------------------------------------------------------------------------------------------------
    ABC          123        1234       Blue     1.1       1           TEXTILE       Active          594.00
    ABC          123        1234       Blue     1.1       1           TEXTILE       Temporary       560.00
    ABC          123        1234       Red      0.5       0           TEXTILE       Active          584.00
    ABC          123        1234       Red      0.5       0           TEXTILE       Temporary       550.00
    ABC          123        1234       Grn      3.3       12          TEXTILE       Active          594.00
    ABC          123        1234       Grn      3.3       12          TEXTILE       Temporary       560.00

What I want to get:

    grouping  |  body  |  fabric  |  color  |  thread  |  detail  |  category  |  ActivePrice  |  TemporaryPrice
    ------------------------------------------------------------------------------------------------------------
    ABC          123        1234       Blue     1.1       1           TEXTILE       594.00            560.00
    ABC          123        1234       Red      0.5       0           TEXTILE       584.00            550.00
    ABC          123        1234       Grn      3.3       12          TEXTILE       594.00            560.00

Possible duplicate of Pivoting in DB2

Clockwork-Muse
– Clockwork-Muse

2019-10-25 21:37:48 +00:00
Commented Oct 25, 2019 at 21:37 — Clockwork-Muse
– Clockwork-Muse, Commented Oct 25, 2019 at 21:37

Gordon Linoff · Accepted Answer · 2019-10-25 19:43:21Z

1

A simple method uses conditional aggregation:

select grouping, body, fabric, color, thread, detail, category, 
      max(case when p.priceType = 'Active' then p.price end) as active_price,
      max(case when p.priceType = 'Temporary' then p.price end) as temporary_price
from ordering offs inner join
     pricing p
     on offs.body = p.bodyp
where priceType in ('Active', 'Temporary') and
      offs.category in ('A', 'B', 'C')
group by grouping, body, fabric, color, thread, detail, category;

edited Oct 25, 2019 at 19:43

answered Oct 25, 2019 at 17:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Geoff_S Over a year ago

This seems to still be giving me separate rows, only one will have active and temp will be null and vice versa

Geoff_S Over a year ago

I think it's because I'm grouping by price which creates a distinctino between the two rows

Gordon Linoff Over a year ago

@TomN. . . . I removed those columns from the SELECT, but not the GROUP BY. It is now fixed.

Collectives™ on Stack Overflow

grouping db2 results of multiple rows to give columns for the different results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related