CODEWITHSUNDEEP
sqlsql-server
user1415173
user1415173

Reputation: 151

Querying a table in SQL Server based on permutation of column2 and 3

I have a table like this:

col1    col2    col3
111     1       1
222     1       0
333     0       1
444     0       0

Here col2 = 1 means col1 is commercial, col3 = 1 means col1 is retail as well. How do I get a result like below?

ID      Description 
111     Commercial  
111     Retail
222     Commercial  
333     Retail

Upvotes: 7

Views: 124

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Can be done with UNPIVOT also:

DECLARE @t TABLE
    (
      col1 INT ,
      col2 INT ,
      col3 INT
    )
INSERT  INTO @t
VALUES  ( 111, 1, 1 ),
        ( 222, 1, 0 ),
        ( 333, 0, 1 ),
        ( 444, 0, 0 )

SELECT  col1 ,
        CASE WHEN col = 'col2' THEN 'Commercial'
             ELSE 'Retail'
        END AS Description
FROM    @t UNPIVOT( r FOR col IN ( [col2], [col3] ) ) u
WHERE   r <> 0

Upvotes: 0

Azar
Azar

Reputation: 1867

Uses almost the same as above but in a single result set

Select ID = col1, t.Description
from MyTable
cross apply (select Description = 'Commercial' where col2 = 1   union 
             select Description = 'Retail' where coll3 = 1)t

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726929

You can do it with a UNION ALL:

SELECT ID = col1, 'Commercial' FROM MyTable WHERE col2=1
    UNION ALL
SELECT ID = col1, 'Retail' FROM MyTable WHERE col3=1

Upvotes: 8

Related Questions