sql server - How to make rows to column using sql pivot -

how make flexible pivot referencing data below:

current records

year    month   week    sales      role 2017      1      1      1,000.00    sm 2017      1      1      1,000.00    ps 2017      1      2      1,001.00    ps     2017      1      3      1,002.00    ps  2017      1      4      1,003.00    ps  2017      2      1      1,004.00    ps  2017      2      2      1,005.00    ps  2017      2      3      1,006.00    ps  2017      2      4      1,007.00    ps 

desire output

year  role     1-1       1-2       1-3       1-4      2-1       2-2       2-3       2-4           2017   ps   1,000.00  1,001.00  1,002.00  1,003.00  1,004.00  1,005.00  1,006.00  1,007.00 2017   sm   1,000.00   

current i'm using mssql server 2016. hoping positive response.

many examples of dynamic pivots, here quick answer.

you may notice order cast(replace(col,'-','.') money). ensure proper order of columns (not 1,10,11,2,3)

dynamic sql

declare @sql varchar(max) = stuff((select ',' + quotename(col)                                      (                                           select distinct col=concat(month,'-',week) #yourtable                                           )                                     order cast(replace(col,'-','.') money)                                     xml path('')),1,1,'')   select  @sql = ' select [yaxis1] [year],[yaxis2] [role],' + @sql  + ' (         select yaxis1 = year               ,yaxis2 = role               ,xaxis  = concat(month,''-'',week)               ,value  = sales           #yourtable      )  pivot (sum(value) [xaxis] in (' + @sql + ') ) p' --print  @sql exec(@sql); 

returns

using sample data

create table #yourtable  (year int,month int,week int,sales decimal(10,2), role varchar(25)) insert #yourtable values (2017,1,1,1000.00,'sm'), (2017,1,1,1000.00,'ps'), (2017,1,2,1001.00,'ps'),  (2017,1,3,1002.00,'ps'),  (2017,1,4,1003.00,'ps'),  (2017,2,1,1004.00,'ps'),  (2017,2,2,1005.00,'ps'),  (2017,2,3,1006.00,'ps'),  (2017,2,4,1007.00,'ps')