Unpivot with column name as date

Question

I have the following data in a crosstable format:

prod_codigo  esps_fecini  day1    day2    day3    day4    day5  day6  day7
1077         2018-11-12   200.00  200.00  250.00  250.00  0.00  0.00  0.00
...

I need to convert the data to something like this:

prod_codigo  esps_fecini   Bins
1077         2018-11-12    200.00
1077         2018-11-13    200.00
1077         2018-11-14    250.00
1077         2018-11-15    250.00
1077         2018-11-16    0.00
1077         2018-11-17    0.00
1077         2018-11-18    0.00
...

How can I achieve this?

I'm using the following query, but I can't find a way to sum the dates. I'm using SQL Server 2008

select prod_codigo,esps_fecini,U.Bins
from dba.estimprodsemana
unpivot
(
Bins
for datos in (esps_cadia1,esps_cadia2,esps_cadia3,esps_cadia4,esps_cadia5,esps_cadia6,esps_cadia7)
) U

Answer 1

If you do want to unpivot for this query, you can do it like this:

declare @t table (prod_codigo int,esps_fecini date,day1 decimal(5,2),day2 decimal(5,2),
                  day3 decimal(5,2),day4 decimal(5,2),day5 decimal(5,2),day6 decimal(5,2),
                  day7 decimal(5,2))
insert into @t(prod_codigo,esps_fecini,day1,day2,day3,day4,day5,day6,day7) values
(1077,'20181112',200.00,200.00,250.00,250.00,0.00,0.00,0.00)

select
    prod_codigo,
    newDay,
    Value
from
    @t
        unpivot
    (Value for Offset in (day1,day2,day3,day4,day5,day6,day7)) u
        cross apply
    (select DATEADD(day,CONVERT(int,SUBSTRING(Offset,4,1))-1,esps_fecini) as newDay) v

Where we unpivot first and then work out how to extract a usable number from the resulting data (rather than metadata - the column names) to adjust the date value.

Result:

prod_codigo newDay     Value
----------- ---------- ---------------------------------------
1077        2018-11-12 200.00
1077        2018-11-13 200.00
1077        2018-11-14 250.00
1077        2018-11-15 250.00
1077        2018-11-16 0.00
1077        2018-11-17 0.00
1077        2018-11-18 0.00