Extract Month Name and Year from string in SQL server and pivot on them -

i want extract month name , year string in sql server , pivot on them.i having data in below format

id   | reason 123  |  post-close qc audit - december 2015 124  |  pre fund fraud prevention 125  |  post-close qc audit - november 2015 126  |  post-close qc audit - october 2016 127  |  post-close qc audit - november 128  |  post-close qc audit - december 2015 

i need 2 result set this. 1 valid date , year example:

reason

december 2015 november 2015 october 2016 

and second output in format

december 2015  |  november 2015  |  october 2016 123  |  125  |  126 128  |  null |  null 

here's 1 way tackle this:

-- sample data declare @table table (id int, reason varchar(100)); insert @table values (123, 'post-close qc audit - december 2015'), (124, 'pre fund fraud prevention'), (125, 'post-close qc audit - november 2015'), (126, 'post-close qc audit - october 2016'), (127, 'post-close qc audit - november'), (128, 'post-close qc audit - december 2015');  -- solution dateextract (   select id, reason, mo =      substring     (       reason, start, patindex('% [0-9][0-9][0-9][0-9]%', substring(reason,start,8000))+5     )     (     select        id, reason, start = nullif(max(patindex('%'+mo+'% [0-9][0-9][0-9][0-9]%', reason)),0)     @table     cross join      ( values ('january'),('february'),('march'),('april'),('may'),('june'),('july'),              ('august'),('september'),('october'),('november'),('december')) m(mo)     group id, reason   ) prep ), datematrix (   select      [december 2015] = max(case mo when 'december 2015' id end),     [november 2015] = max(case mo when 'november 2015' id end),     [october 2015]  = max(case mo when 'october 2016'  id end)   dateextract   group id ) select *  datematrix not([december 2015] null , [november 2015] null , [october 2015] null);