# 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.

– JNK
Jan 24, 2012 at 14:28
• Would you consider a UNION in a subquery more elegant? :)
– JNK
Jan 24, 2012 at 14:56

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
``````

`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.

• `UNPIVOT` does it with one scan rather than two so should be more efficient. Jan 24, 2012 at 15:20
• 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!) Jan 24, 2012 at 15:31
• Leave yours, it's better, and makes sense to have two answers with two different options.
– JNK
Jan 24, 2012 at 15:32
• I did remove my "the most sense" in the first line since `UNPIVOT` works more efficiently :)
– JNK
Jan 24, 2012 at 15:33
• Not sure why you have the Join? Just the `UNION` should do the job. Jan 24, 2012 at 15:42