sql server - Unpivotting multiple columns - substring of column name as a new column with CROSS APPLY -

i have table following format

year, month, item,  req_qty1, req_qty2 , ....req_qty31 ,converted1, converted2 ....converted31 

where suffix of each column day of month.

i need convert following format, day_of_month numeric suffix of each column

year, month, day_of_month, item, req_qty, converted 

i thought of using cross apply retrieve data, can't use cross apply "day of month"

select a.year, a.month, a.item, b.req_qty, b.converted test cross apply (values    (req_qty1, converted1),  (req_qty2, converted2),  (req_qty3, converted3), ......  (req_qty31, converted31)  )b (req_qty, converted) 

the way found use nested select inner join

select a.year, a.month,  a.day_of_month,  a.item,a.req_qty, d.converted   (select year, month, item, substring(day_of_month,8,2) day_of_month, req_qty test    unpivot      (req_qty day_of_month in ([req_qty1],[req_qty2],[req_qty3],......[req_qty30],[req_qty31])      ) b   )   inner join (select year, month, item, substring(day_of_month,10,2) day_of_month, converted test    unpivot      (converted day_of_month in ([converted1],[converted2],[converted3],....[converted30],[converted31])      ) c   ) d   on d.year = a.year , d.month = a.month , d.item = a.item , d.day_of_month = a.day_of_month 

is there way use cross apply , yet day_of_month out?

this not solution cross apply make bit faster uses bit simpler approach , simpler execution plan.

sql fiddle

ms sql server 2008 schema setup:

create table test_table([year] int, [month] int, [item] int,  req_qty1 int        , req_qty2 int ,req_qty3 int , converted1 int, converted2 int, converted3 int) insert test_table values  ( 2015 ,  1 , 1 , 10 , 20 , 30 , 100 , 200 , 300), ( 2015 ,  2 , 1 , 10 , 20 , 30 , 100 , 200 , 300),  ( 2015 ,  3 , 1 , 10 , 20 , 30 , 100 , 200 , 300) 

query 1:

select *   (     select [year]            ,[month]            ,item           ,vals           ,case when left(n,3) = 'req' substring(n,8 ,2)                 when left(n,3) = 'con' substring(n,10,2)            end day_of_month           ,case when left(n,3) = 'req' left(n,7)                 when left(n,3) = 'con' left(n,9)            end tran_type     test_table t       unpivot (vals n in ([req_qty1],[req_qty2],[req_qty3],                                             [converted1],[converted2],[converted3]))up  )t2 pivot (sum(vals)        tran_type        in (req_qty, converted))p   

results:

| year | month | item | day_of_month | req_qty | converted | |------|-------|------|--------------|---------|-----------| | 2015 |     1 |    1 |            1 |      10 |       100 | | 2015 |     1 |    1 |            2 |      20 |       200 | | 2015 |     1 |    1 |            3 |      30 |       300 | | 2015 |     2 |    1 |            1 |      10 |       100 | | 2015 |     2 |    1 |            2 |      20 |       200 | | 2015 |     2 |    1 |            3 |      30 |       300 | | 2015 |     3 |    1 |            1 |      10 |       100 | | 2015 |     3 |    1 |            2 |      20 |       200 | | 2015 |     3 |    1 |            3 |      30 |       300 |