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]
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.
UNPIVOT for this unless
ubr are both indexed and a relatively low proportion contain the value
(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.