CODEWITHSUNDEEP
sqlsql-serverunpivot
Cristian Avendaño
Cristian Avendaño

Reputation: 477

Unpivot with column name as date

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

Upvotes: 0

Views: 1603

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

If you have more day columns than you care to code or specify, or if the number of day columns is variable, the following will "dynamically" unpivot your data without actually using dynamic SQL.

Clearly UNPIVOT would be more performant

Example

declare @YourTable 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 @YourTable values
(1077,'20181112',200.00,200.00,250.00,250.00,0.00,0.00,0.00)


Select A.prod_codigo
      ,esps_fecini = dateadd(day,-1+replace(Item,'day',''),esps_fecini)
      ,Bins=C.Value
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','decimal(5,2)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') like  'day%'
             ) C

Returns

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

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15175

You had the answer in your question, just a little formatting :)

Also, you need to derive the offset using ROW_NUMBER(), based on natural order, in this case. You can then calculate the date based on the applying th derived field based on column order D1..D7.

DECLARE @T TABLE(ID INT, Date DATETIME, D1 INT, D2 INT, D3 INT, D4 INT, D5 INT, D6 INT, D7 INT)
INSERT @T VALUES(1077,'11/12/2018',200,200,250,250,0,0,0)


SELECT
    ID,
    Date = DATEADD(DAY,DayNumber,Date),
    Bins    
FROM
(
    SELECT 
        ID,
        Date,
        Bins,
        DayNumber = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) - 1
    FROM @T
    UNPIVOT
    (
        Bins FOR Dates in (D1,D2,D3,D4,D5,D6,D7)
    ) AS X
)AS Y

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

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

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use APPLY instead :

select e.prod_codigo, ee.*
from dba.estimprodsemana e cross apply
     ( values (esps_fecini, day1),
              (dateadd(day, 1, esps_fecini), day2),
              (dateadd(day, 2, esps_fecini), day3),
              (dateadd(day, 3, esps_fecini), day3),
              (dateadd(day, 4, esps_fecini), day4),
              (dateadd(day, 5, esps_fecini), day5),
              (dateadd(day, 6, esps_fecini), day6),
              (dateadd(day, 7, esps_fecini), day7)
     ) ee (esps_fecini, Bins);

Upvotes: 0

Related Questions