Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
    
+1 - I went and learned the UNPIVOT syntax and you posted an example right afterwards :P –  JNK Jan 24 '12 at 15:31
    
Just to add: UNPIVOT is just sugar for APPLY VALUES, which can be faster and more flexible. See bradsruminations.blogspot.com/2010/02/… and bradsruminations.blogspot.com/2010/02/… –  Paul White Jan 25 '12 at 2:06
    
e.g.: SELECT p.id, u.edition FROM @prod AS p CROSS APPLY ( VALUES ('abr', p.abr), ('ubr', p.ubr) ) AS u (edition, v) WHERE u.v = 1; –  Paul White Jan 25 '12 at 2:14
    
@SQLKiwi - Thanks. I knew about simulating UNPIVOT with a cross join but that's a new one for me. –  Martin Smith Jan 25 '12 at 9:47

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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