SQL help please--Pivot

[John_Snow]

Hi ,

Could you help me in getting the below results,

For ex i have data like

ID Pink Blue Red Green
1       1     0      1     0
2       0     0      1     1
3       1     1      0     0
4       0     0     1      0
5       1     1     1      0

Expected output:
ID Color
1 Pink
1 Red
2 Red
2 Yellow
3 Pink
3 Black
4 Red
5 Pink
5 Black
5 Red

[ZeusClan]

CREATE TABLE ADHOC.UNPIVOT_TBL
(
    Id INT ,
     Pink  INT,
    Blue INT,
    Red INT,
    Green INT
)

INSERT INTO ADHOC.UNPIVOT_TBL VALUES (1,1,0,1,0)
INSERT INTO ADHOC.UNPIVOT_TBL VALUES (2,0,0,1,1)
INSERT INTO ADHOC.UNPIVOT_TBL VALUES (3,1,1,0,0)
INSERT INTO ADHOC.UNPIVOT_TBL VALUES (4,0,0,1,0)
INSERT INTO ADHOC.UNPIVOT_TBL VALUES (5,1,1,1,0)

SELECT * FROM ADHOC.UNPIVOT_TBL

SELECT * FROM (
SELECT ID, COLOR,BITS
FROM ADHOC.UNPIVOT_TBL
UNPIVOT
(
    BITS
    FOR COLOR in (Pink, Blue, Red, Green)
) AS TBLUnpivot
) DT WHERE BITS=1