Unpivot table in SQL Server

Question

I have a table with following columns:

SeqNo, Date, Code, Val1, Val2, Val3,.. Val20

I need to get this representation (I assume I should unpivot table part from Val1 to Val20):

SeqNo, Date, Code, Val

where all Val1 ..Val20 columns go to Val column.

And moreover I need to change Date column values:

For "Val1" value in "Date" shouldn't be changed.
For "Val2" the "Date" value should be decreased by 1 day.
For "Val3" decrease by 2 days, etc.

Please provide some sample data to work on, set up a SQLFiddle — redsoxlost, May 20 '14 at 11:25

score 2 · Accepted Answer · answered May 20 '14 at 11:29

You can do the pivot manually with a cross join and case statement. Your version has a twist to it, because of the date column:

with nums as (
      select 1 as n union all
      select n + 1
      from nums
      where n < 20
    )
select t.seqno, dateadd(day, 1 - nums.n, t.date), t.code,
       (case when nums.n = 1 then val1
             when nums.n = 2 then val2
             . . .
             when nums.n = 20 then val20
        end) as val
from table t cross join
     nums;

Unpivot table in SQL Server

1 Answers1

Linked