sql server - Can data be transformed in the way below using SQL? I'm not sure a pivot would even work -

the users want data shown this:

tax       description             ml   total    link  link      link     link      link     link code                                   rate     code  rate               rate      code     rate  sc001     abbeville county, sc    y    0.0700   sc    0.0600    sclo1    0.0100 sc002     aiken county, sc        y    0.0800   sc    0.0600    sccp1    0.0100    scec1    0.0100 

but data stored in database this:

tax     description             ml  link            link code                                rate    link    description  sc001   abbeville county, sc    y   0.0600  sc      south carolina state             sc001   abbeville county, sc    y   0.0100  sclo1   local option 1%                  sc002   aiken county, sc        y   0.0600  sc      south carolina state             sc002   aiken county, sc        y   0.0100  sccp1   capital projects 1%              sc002   aiken county, sc        y   0.0100  scec1   education capital improvement 

i how use pivot what's stumbling me fact want see link rate beside each tax code. possible?

i'm doing in sql server 2012 , using ssrs or crystal reports, whichever can accomplish this.

i did similar in sql. here code - did not modify fit columns, should starting point.

select  [report_id]   ,[id]   ,[label]   ,[rate]   ,[per_trip]   ,[per_mile]   ,[per_hour]   ,[per_year]  table1  declare @dynamicpivotquery nvarchar(max) declare @column1name nvarchar(max) declare @column2name nvarchar(max)  --get distinct values of pivot column  select @column1name= isnull(@column1name + ',','')     + quotename(label + ' column1') (select distinct label [costmodel].[tractor_cost_report] ) labels  select @column2name= isnull(@column2name + ',','')     + quotename(label + ' column2') (select distinct label [costmodel].[tractor_cost_report] ) labels  --prepare pivot query using dynamic  -- column names in query values of [label] field in rows of data set @dynamicpivotquery =  n' select id,     max([wages , benefits pertrip]) wagesbenefitsamt,           max([fuel & oil pertrip]) fueloilamt,         max([licence pertrip]) licenceamt,     max([insurance pertrip]) insuranceamt,            max([monthly payment pertrip])  monthlypaymentamt,     max([profit pertrip]) profitamt,     max([wcb (%) rate]) wcbrate,     max([wcb (%) pertrip]) wcbamt,     max([ei ($) pertrip]) eiamt,     max([cpp ($) pertrip]) cppamt,     max([statutory holiday ($) pertrip]) statpayamt,     max([vacation (%) rate]) vacationrate,     max([vacation (%) pertrip]) vacationamt,     max([pension ($) rate]) pensionrate,     max([pension ($) pertrip]) pensionamt  (     select id,          rate,         per_trip,         label + '' pertrip'' rowpivot1,         label + '' rate'' rowpivot2      [table1] ) pivot(sum(per_trip)        rowpivot1 in (' + @column1name + ')) pvttable1 pivot(sum(rate)        rowpivot2 in (' + @column2name + ')) pvttable2  group id'  --execute dynamic pivot query print @dynamicpivotquery exec sp_executesql @dynamicpivotquery