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:

  • Error adding Foreign Key Constraint
  • Sql Server stored procedures
  • How to use GETDATE() function in SQL Server to select a column value?
  • SQL Server delete records that are spaces, zeros, and blanks
  • Change Database Collation after migration?
  • How to update datetime field in MSSQL using python pyodbc module
    ==  ==========  ===========  =======
    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,A0001,A0002,A0003 FROM STG.TABLE_A)
    (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,
          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
                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.