sql - 将表格列转为行

Question

我有具有以下结构的表。我需要将列转换为行

ID  NAME      2011   2012
1   macbook   2      241
2   macbook   77     214
3   macbook   0      400
4   iphone    442    555

with tbl as (
 select  1 as id, 'macbook' as name, 2 as "2011", 241 as "2012" from dual union
 select  2 as id, 'macbook' as name, 77 as "2011", 214 as "2012" from dual union
 select  3 as id, 'macbook' as name, 0 as "2011", 400 as "2012" from dual union
 select  4 as id, 'iphone' as name, 442 as "2011", 555 as "2012" from dual
)
select * from tbl

我使用 Oracle 数据库，我试图通过使用 unpivot 函数来解决这个问题，但我堆叠了这个

我希望得到这样的结果

ID  NAME    DATE    SALES
1   macbook 2011    2
1   macbook 2012    241
2   macbook 2011    77
2   macbook 2012    214
3   macbook 2011    0
3   macbook 2012    400
4   iphone  2011    442
4   iphone  2012    555

标签： sqloracleoracle11gunpivot

Answer 1

有 2 个子查询，每个查询每年的结果，然后是 UNION：

select * from (
    select t.id, t.name, '2011' as "date", t."2011" as sales from tbl t  
    union all
    select t.id, t.name, '2012' as "date", t."2012" as sales from tbl t
) t
order by t.id, t.name, t."date"

查看演示