I want to apply unpivot function in SQL Server and Sqllite

Question

I have below table

Monday	Tuesday	Wednesday	Thursday	Friday	WeekStartsOn
8	8	8	8	8	2019-09-06

I want to convert it to below table

DayOftheWeek	ActualHrs	WeekStartsOn	TimecardDate
Monday	8	2019-09-06	2019-09-07
Tuesday	8	2019-09-06	2019-09-08
Wednesday	8	2019-09-06	2019-09-09
Thursday	8	2019-09-06	2019-09-10
Friday	8	2019-09-06	2019-09-11

The logic To add **TimecardDate **is for Monday-->WeekstartsOn+1, Tuesday--> WeekStartsOn+2 .... , Friday--> WeekstartsOn+5

I have tried below Query

SELECT ActualHrs,DayOftheWeek,WeekStartsOn
FROM (select Monday,Tuesday,Wednesday,Thursday,Friday,WeekStartsOn
from itplanning.enriched.timecard_enriched_current) as cp 
UNPIVOT 
( 
ActualHrs FOR DayOftheWeek IN (Monday,Tuesday,Wednesday,Thursday,Friday ) 
) 
AS PivotTable
where ActualHrs<>0

with this Above query, I have got below table

DayOftheWeek	ActualHrs	WeekStartsOn
Monday	8	2019-09-06
Tuesday	8	2019-09-06
Wednesday	8	2019-09-06
Thursday	8	2019-09-06
Friday	8	2019-09-06

I am unable to do the TimecardDate column.

Actual table is below

Monday	Tuesday	Wednesday	Thursday	Friday	WeekStartsOn
8	8	8	8	8	2019-09-06

Expected Table is below

DayOftheWeek	ActualHrs	WeekStartsOn	TimecardDate
Monday	8	2019-09-06	2019-09-07
Tuesday	8	2019-09-06	2019-09-08
Wednesday	8	2019-09-06	2019-09-09
Thursday	8	2019-09-06	2019-09-10
Friday	8	2019-09-06	2019-09-11

[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Apr 27 '23 at 16:46
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Apr 27 '23 at 17:06

siggemannen · Accepted Answer · 2023-04-28T22:00:40.570

0

You can do something like this instead:

SELECT  v.d,v.hours,DATEADD(DAY, v.addDay, WeekStartsOn), WeekStartsOn
FROM itplanning.enriched.timecard_enriched_current
CROSS APPLY (
  VALUES(Monday,'Monday', 1),(Tuesday,'Tuesday', 2),(Wednesday, 'Wednesday', 3),(Thursday, 'Thursday', 4),(Friday, 'Friday', 5)
 ) v(hours, d, addDay)
where hours<>0

I unpivot manually and creates a two column table containing week and how many days to add to start of the week

Edit: added an alternative that does it with pivot syntax

select *
,   dateadd(day, case DayOftheWeek when 'monday' then 1 when 'tuesday' then 2 when 'wednesday' then 3 when 'thursday' then 4 when 'friday' then 5 end, weekstartson) AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
UNPIVOT 
( 
ActualHrs FOR DayOftheWeek IN (Monday,Tuesday,Wednesday,Thursday,Friday ) 
) 
AS PivotTable

Edit #3 (SQLite edition):

with cte as (
    select monday, 'monday', 1 AS addition, weekstartson
    from timecard_enriched_current
    union
    select tuesday, 'tuesday', 2, weekstartson
    from timecard_enriched_current
    union
    select wednesday, 'wednesday', 3, weekstartson
    from timecard_enriched_current
    union
    select thursday, 'thursday', 4, weekstartson
    from timecard_enriched_current
    union
    select friday, 'friday', 5, weekstartson
    from timecard_enriched_current
    ) 
select *, DATE(weekstartson, '+' || addition || ' days') AS TimecardDate
from cte
order by addition

edited Apr 28 '23 at 22:00

answered Apr 27 '23 at 17:04

siggemannen

3,884
2
6
24

I have tried this query but it is showing " Invalid Column ActualHrs" but ActualHrs present in DB. – Sourav Guha Apr 27 '23 at 17:20
sorry, try this version :) – siggemannen Apr 27 '23 at 17:24
I'm trying this in Sqllite but it's not working. Is there any other function to do this? – Sourav Guha Apr 28 '23 at 21:04
added a version that uses unpivot, but not sure it works with sqllite – siggemannen Apr 28 '23 at 21:16
It is not working in sqllite – Sourav Guha Apr 28 '23 at 21:39
Ok, added another version :D – siggemannen Apr 28 '23 at 22:01
Well. That version does work for sql server of course. I don't think you can get same code that works in both because of the difference in syntax and functions – siggemannen Apr 29 '23 at 06:07

I want to apply unpivot function in SQL Server and Sqllite

1 Answers1