Pivot Multiple Rows

I have a table similar to the following: id key str_val date_val num_val 1 A a 1 B b 1 C 2012-01-01 1 D

Solution 1:

You are 90% of the way there:

with cte as (
    select 
        id, 
        case [key] 
            when'A' then str_val 
        endas A, 
        case [key] 
            when'B' then str_val 
        endas B, 
        case [key] 
            when'C' then date_val 
        endas C, 
        case [key] 
            when'D' then num_val 
        endas D,
        case [key] 
            when'E' then str_val 
        endas E 
    from test_table
)
select id, max(A) as A, max(B) as B, max(C) as C, max(D) as D, max(E) as E
from cte
groupby id

Solution 2:

As long as id and key are a unique combination per table then you could write your query like:

SELECT ta.str_val as A,
       tb.str_val as B,
       tc.date_val as C,
       td.num_val as D,
       te.str_val as E
FROM (SELECTDISTINCT id FROM test_table) ids
LEFT JOIN test_table ta ON ids.id = ta.id AND ta.key = 'A'
LEFT JOIN test_table tb ON ids.id = tb.id AND tb.key = 'B'
LEFT JOIN test_table tc ON ids.id = tc.id AND tc.key = 'C'
LEFT JOIN test_table td ON ids.id = td.id AND td.key = 'D'
LEFT JOIN test_table tc ON ids.id = te.id AND te.key = 'E';

In this query you get all the IDs (if you can reply on column 'A' always being there you can start with that instead). Then you have to join on each key for the given id.

If you cannot rely on the data type of the key, i.e. A may be String or Date, then you have to use the following for each select:

COALESCE(ta.str_val,TO_CHAR(ta.date_val,'DD-MM-YYYY'),TO_CHAR(ta.num_val)) A,
COALESCE(tb.str_val,TO_CHAR(tb.date_val,'DD-MM-YYYY'),TO_CHAR(tb.num_val)) B,
...
etc.