SQL algorithm question to convert a table

Question

i have a table like this:

number  stuff1  stuff2  stuff3
-------------------------------
1       x       y
2       a
3       b   
4       c       d       e

i want to use a sql statement to turn it into this:

number  stuff1  stuff2  stuff3
-------------------------------
1       x
1               y   
2       a
3       b   
4       c
4               d   
4                       e

i am having trouble building the sql statement. please help!

Answer 1

Let's assume that your columns are called ID, field1, field2 and field3:

SELECT * FROM (
  SELECT ID, field1, NULL, NULL FROM table WHERE NOT (field1 IS NULL)
  UNION
  SELECT ID, NULL, field2, NULL FROM table WHERE NOT (field2 IS NULL)
  UNION
  SELECT ID, NULL, NULL, field3 FROM table WHERE NOT (field3 IS NULL)
) mySubQuery
ORDER BY ID

(untested; exact syntax might vary depending on the database engine used)