Hi @Abhishek Jaiswal
Check this sample query:
CREATE TABLE #SAMPLE(ID INT,Owner INT,Supporter INT,Member INT,Connector INT,Country VARCHAR(10))
INSERT INTO #SAMPLE VALUES
(1,0,1,1,0,'ABC'),
(2,1,0,0,1,'XYZ'),
(3,1,1,0,0,'ABC'),
(4,0,0,0,0,'IJK'),
(5,1,1,1,1,'AAA')
SELECT * FROM #SAMPLE
SELECT ID,C.Column_Name AS [Values],Country
FROM #SAMPLE CROSS APPLY(VALUES('Owner',Owner)
,('Supporter',Supporter)
,('Member',Member)
,('Connector',Connector))C(Column_Name,Column_Value)
WHERE C.Column_Value<>0
UNION
SELECT ID ,'',Country
FROM #SAMPLE
WHERE Owner=0 AND Supporter=0 AND Member=0 AND Connector=0
Also, you could use UNPIVOT like this:
SELECT ID,Column_Name AS [Values],Country
FROM #SAMPLE UNPIVOT(Column_Value FOR Column_Name IN(Owner,Supporter,Member,Connector))U
WHERE Column_Value<>0
UNION
SELECT ID ,'',Country
FROM #SAMPLE
WHERE Owner=0 AND Supporter=0 AND Member=0 AND Connector=0
Output:
Best regards,
LiHong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.