sql server - sql how do i convert a time series data to a hoc -

i've sql table of format -

company size segment        date     abc     large cap           01-mar-98     abc     large cap           01-sep-98     abc     mid cap             01-mar-99     abc     mid cap             01-sep-99     abc     large cap           01-mar-00     

i want convert format - end date last date of each period. example, start date 1st 'large cap' period 01-mar-98 , end date period 28-feb-99.

security    size segment   start date   end date        abc        large cap       01-mar-98    28-feb-99     abc        mid cap         1-mar-99     28-feb-00     abc        large cap       1-mar-00     null     

how do in sql server?

thank you.

try this:

create table b (id int identity primary key, [company] varchar(3), [size segment] varchar(9), [date] datetime) ;  insert b ([company], [size segment], [date]) values ('abc', 'large cap', '1998-03-01 00:00:00'), ('abc', 'large cap', '1998-09-01 00:00:00'), ('abc', 'mid cap', '1999-03-01 00:00:00'), ('abc', 'mid cap', '1999-09-01 00:00:00'), ('abc', 'large cap', '2000-03-01 00:00:00') ;  ;with tmp ( select id, company, [size segment], [date], row_number() over(order date) row_index b ) select b.*, dateadd(dd, -1, t2.[date]) b  left join tmp t1 on t1.id = b.id left join tmp t2 on t1.row_index + 1 = t2.row_index t1.[size segment] <> t2.[size segment] union select top 1 *, null b order [date] desc