問(wèn)題描述

我在 unpivot sql 語(yǔ)句上遇到問(wèn)題，下表是它的樣子:

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

我想旋轉(zhuǎn) A0001、A0002 和 A0003.為 HEADER、SEQUENCE 和 DATA 再創(chuàng)建 3 列.下面是我預(yù)期的表格會(huì)變成這樣:

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

下面是我嘗試過(guò)的sql語(yǔ)句:

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

我寫(xiě)的SQL只允許顯示pivot后的數(shù)據(jù)，對(duì)于HEADER和SEQUENCE字段我不知道怎么寫(xiě)

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

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

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

推薦答案

應(yīng)用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 函數(shù)，也可以簡(jiǎn)單地使用 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

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