>我有一个结构的小表:
DayRecordID int
InTime_1 datetime
Dept1 int
OutTime_1 datetime
Reason_1 int
InTime_2 datetime
Dept2 int
OutTime_2 datetime
Reason_2 int
InTime_3 datetime
Dept3 int
OutTime_3 datetime
Reason_3 int
InTime_4 datetime
Dept4 int
OutTime_4 datetime
Reason_4 int
InTime_5 datetime
Dept5 int
OutTime_5 datetime
Reason_5 int
InTime_6 datetime
Dept6 int
OutTime_6 datetime
Reason_6 int
我意识到这不是最理想的表,但在我废弃并重做之前,有人会告诉我如何最好地遍历 Dept1、Dept2、Dept3、Dept4、Dept5、Dept6 列并返回非 NULL 的值?我不知道是查询让我感到困惑还是将与之一起使用的子查询。
谢谢!
正如评论中提到的,最好的办法是以更不透视的方式存储它。 如果此结构中已有数据,则可以使用交叉应用将现有内容转换为非透视结构(因为 UNPIVOT 函数不会对多个列进行透视):
select DayRecordID, Dept, InTime, OutTime, Reason
from tb
cross apply
(
values
(Dept1, InTime_1, OutTime_1, Reason_1),
(Dept2, InTime_2, OutTime_2, Reason_2),
(Dept3, InTime_3, OutTime_3, Reason_3),
(Dept4, InTime_4, OutTime_4, Reason_4),
(Dept5, InTime_5, OutTime_5, Reason_5),
(Dept6, InTime_6, OutTime_6, Reason_6)
) c (Dept, InTime, OutTime, Reason)
where Dept IS NOT NULL
OR InTime IS NOT NULL
OR OutTime IS NOT NULL
OR Reason IS NOT NULL;
SQL 小提琴演示在这里
(感谢@bluefeet在这里的多列非枢轴回答:https://stackoverflow.com/a/18617372/4313829)