Select statement to take comment field and make seperate lines

Transact-SQL
1

I have a table bmprdstr that holds a item number and has 3 fields in the record that are for comments.

bmprdstr.item_no, bmprdstr.cmt1, bmprdstr.cmt2, bmprdstr.cmt3

Is there a way to have a select statement return

Seq_no item_no Comment
1 PART Comment line 1
2 PART Comment line 2
3 PART Comment line 3

2 
create table #bmprdstr(
item_no int identity(1,1) not null primary key 
,cmt1 varchar(10) null
,cmt2 varchar(10) null
,cmt3 varchar(10) null)

insert into #bmprdstr(cmt1,cmt2,cmt3)
values('cm1','cm2','cm3')
,('cm1 rec2','cm2 rec2','cm3 rec3')


select ROW_NUMBER()OVER(PARTITION BY item_no ORDER BY cmt1,cmt2,cmt3) as Seq_no
        ,b.item_no
        ,c.Comment
from #bmprdstr as b
cross apply
(select b.cmt1 union all
 select b.cmt2 union all
 select b.cmt3) as c(Comment)

output

Seq_no item_no Comment
1 1 cm1
2 1 cm2
3 1 cm3
1 2 cm1 rec2
2 2 cm2 rec2
3 2 cm3 rec3

dbfiddle

the same output if you want to use Table Value constructor in the cross apply

SELECT ROW_NUMBER()OVER(PARTITION BY item_no ORDER BY cmt1,cmt2,cmt3) as Seq_no
        ,b.item_no
        ,c.Comment
FROM #bmprdstr as b
CROSS APPLY
      ( VALUES (b.cmt1)
              ,(b.cmt2)
              ,(b.cmt3)
      ) as c(Comment)
3

You'll probably want to add:

WHERE Comment > ''

So you don't list missing/empty comments.

1 Like
4

Good point @ScottPletcher , thanks!