Select multiple rows from one row based on column values

Posted on

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.

Answer :

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.

Leave a Reply

Your email address will not be published.