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                               1
2     A      c
2     E      d
2     C                 2012-01-02
2     D                                2

I need it to look like:

id    A      B      C             D       E
1     a      b      2012-01-01    1
2     c             2012-01-02    2       d

Basically, each key needs to become it's own column

I tried and failed to use the PIVOT command usefully, and I'm currently trying to accomplish this with a case statement. i.e.

select
    id,
    case key
        when 'A'
        then str_val
    end as A,
    case key
        when 'C'
        then date_val
    end as C
    --etc.
from test_table

However I can't figure out how to combine the table rows after this runs. I'm stuck with:

id    A      B      C             D       E
1     a
1            b
1                   2012-01-01
1                                 1

Any thoughts or input that could help me out? Thanks in advance.

回答1:

You are 90% of the way there:

with cte as (
    select 
        id, 
        case [key] 
            when 'A' 
            then str_val 
        end as A, 
        case [key] 
            when 'B' 
            then str_val 
        end as B, 
        case [key] 
            when 'C' 
            then date_val 
        end as C, 
        case [key] 
            when 'D' 
            then num_val 
        end as D,
        case [key] 
            when 'E' 
            then str_val 
        end as 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
group by id

回答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 (SELECT DISTINCT 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.