問題描述

我在 unpivot sql 語句上遇到問題，下表是它的樣子:

I am facing problem on unpivot sql statement, below is the table how its look like:

ID  A0001       A0002      A0003
==  =========== ========== ==========
S1  100         200        300 
S2  321         451        234
S3  0           111        222

我想旋轉 A0001、A0002 和 A0003.為 HEADER、SEQUENCE 和 DATA 再創建 3 列.下面是我預期的表格會變成這樣:

I want to pivot A0001,A0002 and A0003. Create 3 more column for HEADER,SEQUENCE AND DATA. Below is my expected table to become like this:

ID  HEADER      SEQUENCE     DATA
==  ==========  ===========  =======
S1  A0001       1            100 
S1  A0001       2            200
S1  A0001       3            300
S2  A0002       1            321
S2  A0002       2            451
S2  A0002       3            234
S3  A0003       1            111
S3  A0003       2            222

下面是我嘗試過的sql語句:

Below is the sql statement I have try:

SELECT ID,DATA FROM
(SELECT ID,A0001,A0002,A0003 FROM STG.TABLE_A)
UNPIVOT
(DATA FOR B IN (A0001,A0002,A0003)) C

我寫的SQL只允許顯示pivot后的數據，對于HEADER和SEQUENCE字段我不知道怎么寫

The SQL I write only allow to show the data after pivot, for HEADER and SEQUENCE field I have no idea how to write

其次，我還想過濾掉是否有任何樞軸列為零將被過濾掉.例如，ID = S3，A0001 為 0，因此過濾零只得到其他大于零的字段

Secondly, I would also like to filter out if any pivot column is zero will be filter out. Example, ID = S3, A0001 is 0,therefore filter the zero and only get other fields which is greater than zero

推薦答案

應用unpivot后可以有這種情況，如下圖-

You can have this condition after appling unpivot as below -

SELECT ID, DATA, header
  FROM (SELECT ID, A0001, A0002, A0003 FROM STG.TABLE_A) 
        UNPIVOT(DATA FOR header IN (A0001, A0002, A0003)) C
 where data <> 0

在這種情況下，您可以使用 unvipot 函數，也可以簡單地使用 union ，如下所示 -

You can either use the unvipot function or you can simply use union also in this case as below -

   select id, header, sequence, data
    from (select @i := if(@lastid != id, 1, $i + 1) as sequence,
           @lastid := id,
           id,
           header,
           data
      from (

            select ID, 'A0001' as Header, A0001 as DATA
              from your_table_name
             where A0001 <> 0
            union all
            select ID, 'A0002' as Header, A0002 as DATA
              from your_table_name
             where A0002 <> 0
            union all
            select ID, 'A0003' as Header, A0003 as DATA
              from your_table_name
             where A0003 <> 0
            )t_1
            ORDER BY ID, DATA
    ) t_2

這篇關于SQL 逆透視表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！