0

Wanted to know if I could "artificially" insert new records when a record contains a value for a specific column. For example say I have this table in my database with the following two records:

Column1  Column2  Column3
-------------------------
DataA1   DataA2   null
DataB1   DataB2   DataB3

Now Column3 is the column I want to trigger an extra row if there is a value. Column3 is essentially Column2 but with another value (this is non-normalized and I can't change it so I need to resort to a query instead). So I want to create a query that returns 3 rows using the example above and it should come out like this:

DataA1   DataA2
DataB1   DataB2
DataB1   DataB3

How do I write my sql to return the results above?

Los Morales
  • 2,061
  • 7
  • 26
  • 42

3 Answers3

4

Use union all:

SELECT Column1, Column2
FROM TableName 
WHERE Column3 IS NULL

UNION ALL

SELECT Column1, Column3
FROM TableName 
WHERE Column3 IS NOT NULL
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Actually my title is incorrect. I'm not trying to insert into a table but into the resultset returned by the sql query. Hence I need to generate the additionaly records in my select sql. sorry for confusion, english is not my native language. – Los Morales Feb 22 '18 at 17:43
  • hehe...you edited your answer just as I was posting mine. :) – Sean Lange Feb 22 '18 at 17:46
1

Not totally sure what you want here but I think you are looking for something like this.

select Column1
    , Column2
from SomeTable
where Column2 is not null

UNION ALL

select Column1
    , Column3
from SomeTable
where Column3 is not null
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
1

You could use a UNION statement to merge a result set that uses the third column as the second column when the third column is not null:

SELECT column1, column2
FROM Sample
UNION
SELECT column1, column3 
FROM Sample
WHERE column3 IS NOT NULL

http://sqlfiddle.com/#!9/42ca15/6

GVIrish
  • 361
  • 1
  • 6