TSQL - 帮助 UNPIVOT [英] TSQL - Help with UNPIVOT
本文介绍了TSQL - 帮助 UNPIVOT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在转换这个旧表中的数据:Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit)
这些位字段不可为空,并且可能所有四个位字段都可以为 1.
I am transforming data from this legacy table:
Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit)
These bit fields are not nullables and, potentially, all four bit fields can be 1.
我怎样才能取消旋转这个东西,以便我最终为每个位字段 = 1 生成一个单独的行.例如,如果原始表看起来像这样...
How can I unpivot this thing so that I end up with a separate row for each bit field = 1. For instance, if the original table looks like this...
ID, PhoneNumber, IsCell, IsPager, IsDeskPhone, IsFax
----------------------------------------------------
1 123-4567 1 1 0 0
2 123-6567 0 0 1 0
3 123-7567 0 0 0 1
4 123-8567 0 0 1 0
...我希望结果如下:
... I want the result to be the following:
ID PhoneNumber Type
-----------------------
1 123-4567 Cell
1 123-4567 Pager
2 123-6567 Desk
3 123-7567 Fax
4 123-8567 Desk
谢谢!
推荐答案
2005/2008版
SELECT ID, PhoneNumber, Type
FROM
(SELECT ID, PhoneNumber,IsCell, IsPager, IsDeskPhone, IsFax
FROM Phones) t
UNPIVOT
( quantity FOR Type IN
(IsCell, IsPager, IsDeskPhone, IsFax)
) AS u
where quantity = 1
这篇关于TSQL - 帮助 UNPIVOT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文