locked
SQL UNPIVOT RRS feed

  • Question

  • User-2017229834 posted

    Hi,

    I have a table like this:

    DESC A B C D
    Other 1150197 296955 296955 0
    Reverse 0 135 135 0

    And I want 

    Other 0 0
    A 1150197 0
    B 296955 0
    C 296955 0
    D 0 0
    Reverse 0 0
    A 0 0
    B 135 0
    C 135 0
    D 0 0

    Please help.

    Friday, January 19, 2018 11:43 AM

Answers

  • User77042963 posted
    CREATE TABLE mytable(
       [DESC] VARCHAR(7) NOT NULL  
      ,A    INTEGER  NOT NULL
      ,B    INTEGER  NOT NULL
      ,C    INTEGER  NOT NULL
      ,D    BIT  NOT NULL
    );
    INSERT INTO mytable([DESC],A,B,C,D) VALUES ('Other',1150197,296955,296955,0),
      ('Reverse',0,135,135,0);
      ;with mycte as (
     select row_number() Over(Order by [DESC]) rn, * from mytable
     Cross apply (values (Cast('de' as varchar(20)),[DESC]),('A',Cast(A as varchar(20)))
     ,('B',Cast(B as varchar(20)))
     ,('C',Cast(C as varchar(20)))
     ,('D',Cast(D as varchar(20))) ) d(col1,col2)
     )
    
     Select isnull(nullif(col1,'de'),col2) col1
     ,case when col1='de' then'0' else col2 end as col2 
     from mycte
    drop table mytable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 19, 2018 2:57 PM