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.