SQL Unpivot table

Question

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

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

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

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

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

It would be useful if you could show us what you have tried so far and what problems you have. — icedwater
– icedwater, Commented Jun 28, 2013 at 9:13
That doesn't even make sense. Can you confirm that the Header column has the correct values? — Joao Leal
– Joao Leal, Commented Jun 28, 2013 at 9:19
also what version of SQL server are you using? you can use the pivot and unpivot functions - msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Dev N00B
– Dev N00B, Commented Jun 28, 2013 at 9:25

fancyPants · Accepted Answer · 2013-06-28 19:09:14Z

1

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

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

edited Jun 28, 2013 at 19:09

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

answered Jun 28, 2013 at 9:47

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

symcbean Over a year ago

The latter query doesn't need the sequence generator (which could prove to be very unreliable) according to the OP's example.

pratik garg Over a year ago

In Question this sequence is one column and I thought that it would be helpful to provide logic to show sequence in mysql query on groups.

pratik garg Over a year ago

@symcbean Please tell why sequence generation is unreliable.. whether mysql query will give incorrect result if we add sequence generation logic also in the query.

fancyPants Over a year ago

@pratikgarg You're missing an from (...)t_1 ORDER BY ID, DATA in there. And there is no UNPIVOT() in MySQL. Apart from that your answer is correct.

pratik garg Over a year ago

Thanks @tombom for highlighting the unpivot part.. Actually I am working on oracle database and hence I thought in mysql also unpivot option is available.. since Arzozeus tried on this command so I thought that this command is valid and available in Mysql also.. but afterwards I searched and got that this is not valid syntax in Mysql.. Thanks for updating my knowledge on mysql also ..

|

Collectives™ on Stack Overflow

SQL Unpivot table

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related