Get column name as value in row based on values

Question

Hi All,

I need your help to get result as shown in picture with sql query.
Hope you can help me with this.

Regards,

Answer 1

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.