Tsql - Help With Unpivot

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

Solution 1:

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)

Solution 2:

Try this:

DROPTABLE #Phones
CREATETABLE #Phones
(
    Id int,
    PhoneNumber varchar(50),
    IsCell bit,
    IsPager bit,
    IsDeskPhone bit,
    IsFax bit
)

INSERTINTO #Phones VALUES (1, '123-4567', 1, 1, 0, 0)
INSERTINTO #Phones VALUES (2, '123-6567', 0, 0, 1, 0)
INSERTINTO #Phones VALUES (3, '123-7567', 0, 0, 0, 1)
INSERTINTO #Phones VALUES (4, '123-8567', 0, 0, 1, 0)

SELECT Id, PhoneNumber, [Type]
FROM (
    SELECT  Id, PhoneNumber, 
            Cell = IsCell, Pager = IsPager, 
            Desk = IsDeskPhone, Fax = IsFax
    FROM #Phones
) a 
UNPIVOT(
    something FOR [Type] IN (Cell, Pager, Desk, Fax )
) as upvt