.net - SQL 脚本帮助

Question

我有一个这样的名为“Days”的表

Day 1    Day 2    Day 3    Day 4    Day 5    Day 6    Day 7
1        1        1        1        0        0        0
1        1        1        1        0        0        0

其中有相应的详细信息。其中 1 为“真”，0 为假。

DaY 1 - sunday
day 2- monday
day 3 - tuesday
day 4- wednesday
day5 - thursday
day 6 - friday
dayy 7- saturday

1 - denotes there is work on the given day
0 - denotes there is no work on the given day.

鉴于上面的细节，我需要制作列到表的映射并且需要生成一个这样的表，我只需要在其中获取具有标志 1 的数据

1   Sunday 
2   Monday 
3   Tuesday     
4   Wednesday

第一条记录。

Answer 1

WITH    [days] (id, day1, day2, day3, day4, day5, day6, day7) AS
        (
        SELECT  1, 1, 1, 1, 1, 1, 0, 0
        UNION ALL
        SELECT  2, 1, 1, 1, 1, 1, 0, 0
        )
SELECT  id, DATENAME(dw, DATEADD(d, CAST(SUBSTRING(wd, 4, 1) AS INT), '2005-01-01')), work
FROM    [days]
UNPIVOT
        (
        work FOR wd IN
        (day1, day2, day3, day4, day5, day6, day7)
        ) AS up
WHERE   work = 1