SQL Unpivot table

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:

  • sort results by column not row
  • procedure to upload excel into sql server
  • Profiling Linked Server
  • Extremely slow insert OpenQuery performance on SQL Server to MySQL linked server
  • Schedule SQL Job in a user configured time intervals everyday
  • Can you call a webservice from TSQL code?
  • 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

    One Solution collect form web for “SQL Unpivot table”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.