将表列转换为行

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数据库，我试图通过使用un枢轴函数来解决这个问题，但是我对此进行了讨论。

我希望能得到这样的结果

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

Answer 1

试一试:使用un枢轴

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
UNPIVOT (SALES FOR "DATE" IN ("2011" AS '2011', "2012" AS '2012'));

输出:

​

​

Answer 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"

见演示

Answer 3

您可以将表与日期列表交叉连接，然后使用解码提取每一行的相关年销售额。

SELECT 
    t.id,
    t.name.
    t.dt "date",
    DECODE(t.dt, 2011, t."2011", 2012, t."2012", 0) sales
FROM mytable t
CROSS JOIN (
    SELECT 2011 dt FROM DUAL
    UNION SELECT 2012 FROM DUAL
) d
ORDER BY 1,2