SELECT DISTINCT
CASE x.f WHEN 1 THEN s.ProductColour END AS ProductColour
CASE x.f WHEN 2 THEN s.ProductSize END AS ProductSize
FROM shoptransfer s
CROSS JOIN (SELECT 1 UNION ALL SELECT 2) x (f)
This produces the same output as Brian's solution. The idea is to make every row of shoptransfer
output twice without scanning the table twice. That is achieved by cross-joining the table to a tiny tally table created 'on the fly'. When the tally table's first row is current, the query produces a row with a ProductColour value and NULL as ProductSize, and the second time it's the other way round, i.e. ProductColour is NULL and ProductSize contains a value from the shoptransfer
table.
The syntax used for defining the tally table may seem a bit unusual. For those of you not acquainted with that way of aliasing, x
is the subselect's alias, the f
in brackets is the alias for the subselect's single column. In short, the following two definitions are absolutely equivalent to each other:
(SELECT 1 UNION ALL SELECT 2) x (f)
(SELECT 1 AS f UNION ALL SELECT 2) x