发信人: kveye (kv), 信区: Database
标  题: 【SQL问题】如何“unpivot”一个表
发信站: BBS 未名空间站 (Sun Feb 26 08:12:25 2017, 美东)

比如有一个denormalized的TableA:

ID | SPEND_A | SPEND_B | SPEND_C
1             0              10               0
2           25              0               30

如何写一个query来转换TableA成下面这种比较normalized的展现形式:
ID | SPEND_TYPE | Value
1      SPEND_B           10
2     SPEND_A           25
3     SPEND_C           30

Intuitively, 会想到用union，但是实际情况是TableA很大并且有超过100个column。
有什么其他高效的方法来做这种transform呢?

--
※ 修改:·kveye 於 Feb 26 08:13:41 2017 修改本文·[FROM: 73.]
※ 来源:·WWW 未名空间站 网址：mitbbs.com 移动：在应用商店搜索未名空间·[FROM: 73.]

此主题相关图片如下：

发信人: 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.]

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

And, you can use the database system tables to build the SQL:

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 <= X)
   WHERE ID = 1
ORDER BY ID, CAT_NAME;


and:

         IN (SPEND_A AS 'SPEND_A',
             SPEND_B AS 'SPEND_B',
             SPEND_C AS 'SPEND_C'))
:
:
:



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

发信人: olive2008 (olive), 信区: Database
标  题: Re: 【SQL问题】如何“unpivot”一个表
发信站: BBS 未名空间站 (Thu Mar  2 15:47:10 2017, 美东)

你这个在SQL server 里用个 UNPIVOT  的命令就行了。
--
※ 来源:·WWW 未名空间站 网址：mitbbs.com 移动：在应用商店搜索未名空间·[FROM: 174.]