How to insert new rows based on values in other columns in sql server?

I have a dataset like below:

Id   A14_Comment   A15_Comment   A16_Comment
1    Comment1       null           null
2    Comment2       Comment3       Comment4
3    null           Comment5       Comment6
4    null           null           Comment7

What I need to do is to get the output below:

Id   A14_Comment   A15_Comment   A16_Comment   Code
1     Comment1       null           null        A14
2     Comment2       Comment3       Comment4    A14
2     Comment2       Comment3       Comment4    A15
2     Comment2       Comment3       Comment4    A16
3     null           Comment5       Comment6    A15
3     null           Comment5       Comment6    A16
4     null           null           Comment7    A16

As to be seen, my purpose is to add a Code column and duplicate rows by labelling the code. The query below gives me how many times I need to add row with different code for each row, but couldn’t figure out an efficient way to do the rest.

select Id, (
                select count(*)
                from (values (T.A14_Comment), (T.A15_Comment), (T.A16_Comment)) as v(col)
                where v.col is not null and v.col <> ''
           )    from #Comments as T

Any help would be appreciated.

Answer

I would use apply:

select c.*, c.code
from #Comments c cross apply
     (select c.code
      from (values (c.A14_Comment, 'A14'), (c.A15_Comment, 'A15'), (c.A16_Comment, 'A16')
           ) v(col, code)
      where col is not null
     ) c;

The subquery is actually unnecessary. You might find this simpler:

select c.*, v.code
from #Comments c cross apply
     (values (c.A14_Comment, 'A14'), (c.A15_Comment, 'A15'), (c.A16_Comment, 'A16')
     ) v(col, code)
where v.col is not null

Advertisement

Answer