SQL 逆透视表

2021-09-14 00:00:00 sql sql-server unpivot

我在 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

相关文章