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

Question

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  

Answer 1

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

Answer 2

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