TSQL - Help with UNPIVOT

sql, sql-server, t-sql, unpivot

Solution

2005/2008 version

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

see also Column To Row (UNPIVOT)

Problem

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. 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 ``` Thanks!

Original source