Select multiple rows from one row based on column values I have a product table where the value of two columns can imply either one or two actual product results.  If column X = 1 AND Y = 0 return one result, if X = 0 and Y = 1 return one result, if X = 1 AND Y = 1 then return two results.

I want to write a query that will generate two row results for a single row based on the above rule. How can this be done with a SQL query?  Is a UNION the only way?

[EDIT based on comment]

TABLE: PRODUCT

ProductId | ABR | UBR
1 | 1 | 1
2 | 1 | 0
3 | 0 | 1
4 | 1 | 1
5 | 1 | 1


I want a SELECT statement that will generate 8 results from this set.  Basically one result for each instance of either ABR or UBR = 1.

So I would like my result to be:

ProductId | Edition
1 | ABR
1 | UBR
2 | ABR
3 | UBR
4 | ABR
4 | UBR
5 | ABR
5 | UBR


I know I can achieve this using a UNION but I was looking for something more elegant.
UNION does make sense.

You can put the UNION in a subquery which will neaten it up some:

DECLARE @prod TABLE (id int, abr int, ubr int)
INSERT INTO @prod
VALUES
(1 , 1 , 1),
(2 , 1 , 0),
(3 , 0 , 1),
(4 , 1 , 1),
(5 , 1 , 1)

SELECT id, 'ABR' as Ed
FROM @prod
WHERE abr = 1
UNION ALL
SELECT id, 'UBR' as Ed
FROM @prod
WHERE ubr = 1
ORDER BY id


You could also do an UNPIVOT I think but for this simple use case a UNION seems most efficient.