Unpivot table in SQL Server

问题:

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.

回答1:

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;