Get column name as value in row based on values

Abhishek Jaiswal 41 Reputation points
2022-10-07T08:37:04.203+00:00

Hi All,

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

Regards,248359-ecf20a41-37d5-42d7-8096-1e4c7aded1bd.jpeg

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor
    2022-10-07T09:33:44.817+00:00

    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:
    248456-image.png

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful