LDD LDD - 1 month ago 7
SQL Question

SQL - one row to split into two columns snake

I have the following table ( there can be up to 15 comment lines)

DeliveryNo|LineNo |CommentNo |CommentText
-------- |-------|----------|------------
2214518 |1 |1 |pre SS17 order
2214518 |1 |2 |CHECK DELIVERY DATE
2214518 |1 |3 |02/11/2016
2214518 |1 |4 |For Attention
2214518 |1 |5 |Joe Soaps


Need the output to look like this

DeliveryNo| LineNo|Comment1 |Comment2
----------|-------|-------------------|------------
2214518 |1 |pre SS17 order |CHECK DELIVERY DATE
2214518 |1 |02/11/2016 |For Attention
2214518 |1 |Joe Soaps |

Answer

This is how I would have done it ...

if object_id('tempdb..#Test') is not null drop table #Test

create table #Test (DeliveryNo bigint, Line int, CommentNo int, CommentText nvarchar(100))

insert into #Test(DeliveryNo, Line, CommentNo, CommentText)
values
(2214518   ,1      ,1         ,'pre SS17 order'),
(2214518   ,1      ,2         ,'CHECK DELIVERY DATE'),
(2214518   ,1      ,3         ,'02/11/2016'),
(2214518   ,1      ,4         ,'For Attention'),
(2214518   ,1      ,5         ,'Joe Soaps')

select t1.DeliveryNo, 
   t1.Line,
   t1.CommentText,
   t2.CommentText
from #Test t1
left join #Test t2 on t1.CommentNo = t2.CommentNo - 1
where t1.CommentNo % 2 = 1

Result is like you asked for

Comments