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

Question

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 1

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

Answer 2

With a join of the table to a CTE:

with 
  codes as (
    select 'A14' code
    union all
    select 'A15'
    union all
    select 'A16'
  ), 
  idcodes as (
    select comments.id, codes.code
    from comments cross join codes
  )

select * 
from comments c inner join idcodes i
on 
  c.id = i.id
  and (
    c.A14_Comment is not null and i.code = 'A14'
    or
    c.A15_Comment is not null and i.code = 'A15' 
    or
    c.A16_Comment is not null and i.code = 'A16' 
  )

See the demo.
Results:

> 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 

Answer 3

The below query will give the expected result:-

select * From (
select *,'A14' Code from #Comments where A14_Comment is not null union all
select *,'A15' Code from #Comments where A15_Comment is not null union all
select *,'A16' Code from #Comments where A16_Comment is not null) as xData order by ID,Code


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