2

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.

  • More information on your data would be helpful. – JNK Jan 24 '12 at 14:28
  • Would you consider a UNION in a subquery more elegant? :) – JNK Jan 24 '12 at 14:56
2

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
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.