I want to apply unpivot function in SQL Server

bvjveswy  于 2023-04-28  发布在  SQL Server
关注(0)|答案(1)|浏览(172)

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

DayOftheWeekActualHrsWeekStartsOnTimecardDate
Monday82019-09-062019-09-07
Tuesday82019-09-062019-09-08
Wednesday82019-09-062019-09-09
Thursday82019-09-062019-09-10
Friday82019-09-062019-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

DayOftheWeekActualHrsWeekStartsOn
Monday82019-09-06
Tuesday82019-09-06
Wednesday82019-09-06
Thursday82019-09-06
Friday82019-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

DayOftheWeekActualHrsWeekStartsOnTimecardDate
Monday82019-09-062019-09-07
Tuesday82019-09-062019-09-08
Wednesday82019-09-062019-09-09
Thursday82019-09-062019-09-10
Friday82019-09-062019-09-11
sql-server

1条答案

按热度按时间
wfsdck30

wfsdck301#

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

赞(0）分享  回复(0）举报  2023-04-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com