当前在线人数14118
首页 - 分类讨论区 - 电脑网络 - 数据库版 -阅读文章
未名交友
[更多]
[更多]
文章阅读:Re: 【SQL问题】如何“unpivot”一个表
[同主题阅读] [版面: 数据库] [作者:nmamtf] , 2017年02月28日15:41:55
nmamtf
进入未名形象秀
我的博客
[上篇] [下篇] [同主题上篇] [同主题下篇]

发信人: nmamtf (nmamtf), 信区: Database
标  题: Re: 【SQL问题】如何“unpivot”一个表
发信站: BBS 未名空间站 (Tue Feb 28 15:41:55 2017, 美东)

[IN ORACLE:]

CREATE TABLE MYTEST
(
   ID        INTEGER,
   SPEND_A   NUMBER (10, 2),
   SPEND_B   NUMBER (10, 2),
   SPEND_C   NUMBER (10, 2)
);

INSERT INTO MYTEST (ID,
                    SPEND_A,
                    SPEND_B,
                    SPEND_C)
     VALUES (1,
             0,
             10,
             0);

INSERT INTO MYTEST (ID,
                    SPEND_A,
                    SPEND_B,
                    SPEND_C)
     VALUES (2,
             25,
             0,
             30);

INSERT INTO MYTEST (ID,
                    SPEND_A,
                    SPEND_B,
                    SPEND_C)
     VALUES (3,
             100,
             300,
             80);


COMMIT;


SELECT ID,
         CAT_NAME,
         CASE CAT_NAME
            WHEN 1 THEN SPEND_A
            WHEN 2 THEN SPEND_B
            WHEN 3 THEN SPEND_C
         END
            COL
    FROM MYTEST,
         (    SELECT LEVEL CAT_NAME
                FROM DUAL
          CONNECT BY LEVEL <= 3)
   WHERE ID = 1
ORDER BY ID, CAT_NAME;

Here is the result:

ID,CAT_NAME,  COL
1, 1,         0
1, 2,         10
1, 3,         0


OR:

SELECT ID, CAT_NAME, VAL
  FROM MYTEST
       UNPIVOT
          (VAL
          FOR CAT_NAME
          IN (SPEND_A AS 'SPEND_A',
             SPEND_B AS 'SPEND_B',
             SPEND_C AS 'SPEND_C'))
WHERE ID = 1;

You will get:

ID,CAT_NAME,  VAL
1, SPEND_A,   0
1, SPEND_B,   10
1, SPEND_C,   0

You can use my first SQL to convert the original data table. Yes, you should
add one more ref table(s) for it.

The second one for just one time output report.

HTH





--
※ 修改:·nmamtf 於 Feb 28 16:16:37 2017 修改本文·[FROM: 152.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 152.]

[上篇] [下篇] [同主题上篇] [同主题下篇]
[转寄] [转贴] [回信给作者] [修改文章] [删除文章] [同主题阅读] [从此处展开] [返回版面] [快速返回] [收藏] [举报]
 
回复文章
标题:
内 容:

未名交友
将您的链接放在这儿

友情链接


 

Site Map - Contact Us - Terms and Conditions - Privacy Policy

版权所有,未名空间(mitbbs.com),since 1996