Select multiple rows from one row based on column values

Question

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.

• JNK: More information on your data would be helpful.
• JNK: Would you consider a UNION in a subquery more elegant? :)

Answer 1

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
 Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Answer 2

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.

• Martin Smith: UNPIVOT does it with one scan rather than two so should be more efficient.
• Martin Smith: You can simplify your UNPIVOT answer a bit. If you do then I'll remove mine! (edit: I see the bit I refer to has gone now!)
• JNK: Leave yours, it's better, and makes sense to have two answers with two different options.
• JNK: I did remove my "the most sense" in the first line since UNPIVOT works more efficiently :)
• Martin Smith: Not sure why you have the Join? Just the UNION should do the job.