TSQL - 帮助 UNPIVOT [英] TSQL - Help with 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

另见列到行 (UNPIVOT)

这篇关于TSQL - 帮助 UNPIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！