sql - MSSQL - 如果值不为 NULL，则返回列名和值

Question

在 mssql 中，假设我有一个包含以下数据的表...

id   name   monday   tuesday   wednesday   thursday   friday
1    mark    null    chores      null       gym        swim
2    steve   gym     null        class      hockey     null
3    mike    chores  gym         null       null       null

我想要一个 sql 语句，它会返回 id、name 和 day 列，其中值不为空，例如...

id    name    day        value
1     mark    tuesday    chores
1     mark    thursday   gym
1     mark    friday     swim
2     steve   monday     gym
2     steve   wednesday  class
2     steve   thursday   hockey
2     mike    monday     chores
2     mike    tuesday    gym

谢谢。

Answer 1

一种方法是union all，但我更喜欢outer apply:

select t.id, t.name, dayname, value
from t outer apply
     (values ('monday', monday),
             ('tuesday', tuesday),
             ('wednesday', wednesday),
             ('thursday', thursday),
             ('friday', friday),
             ('saturday', saturday),
             ('sunday', sunday)
     ) v(dayname, value)
where value is not null;