sqldb2

SQL - How to transpose several columns?


I have a table in an IBM DB2 database; let's call it table_1. It looks a bit like this:

customer info offer_type_a_sales offer_type_b_sales offer_type_c_sales
AA X 10 20 30
BB Y 0 10 15
CC X 20 0 30

What I'd like to do is transpose the offer type columns to create a single column flagging offer type and a single column showing sales. Does anyone know how best to do this please?

The desired output should look like this:

customer info offer_type sales
AA X a 10
AA X b 20
AA X c 30
BB Y b 10
BB Y c 15
CC X a 20
CC X c 30

Solution

  • There might be a way to unpivot but here's an alternative solution using union.

    select customer, info, 'a' as offer_type, offer_type_a_sales as sales from table_1
    union
    select customer, info, 'b', offer_type_b_sales from table_1
    union 
    select customer, info, 'c', offer_type_c_sales from table_1
    
    customer info offer_type sales
    AA X a 10
    AA X b 20
    AA X c 30
    BB Y a 0
    BB Y b 10
    BB Y c 15
    CC X a 20
    CC X b 0
    CC X c 30

    If you don't want the zero rows then add a where clause.