asked on
ASKER
declare @cols1 varchar(max)
declare @cols2 varchar(max)
declare @cols3 varchar(max)
declare @strSQL varchar(max)
set @cols1 = stuff((select distinct '], [' + ex_date + '_mul' + ', [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols2 = stuff((select distinct '], [' + ex_date + '_mul'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols3 = stuff((select distinct '], [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @strSQL = 'select fromc, toc, ' + @cols1 +
' from (
select ex_date + ''_mul'' as ex_date1,
ex_date + ''_div'' as ex_date2,
fromc,
toc,
rate_mul,
rate_div
from hist_exch_rates
where ex_date between ''20070101'' and ''20110601'' --adjust date accordingly
) o
pivot (max(rate_mul) for ex_date1 in (' + @cols2 + ')) as p1
pivot (max(rate_div) for ex_date2 in (' + @cols3 + ')) as p2'
exec(@strSQL)
ASKER
ASKER
ASKER
declare @cols1 varchar(max)
declare @cols2 varchar(max)
declare @cols3 varchar(max)
declare @strSQL varchar(max)
set @cols1 = stuff((select distinct '], [' + ex_date + '_mul], [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols2 = stuff((select distinct '], [' + ex_date + '_mul'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols3 = stuff((select distinct '], [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @strSQL = 'select fromc, toc, ' + @cols1 +
' from (
select ex_date + ''_mul'' as ex_date1,
ex_date + ''_div'' as ex_date2,
fromc,
toc,
rate_mul,
rate_div
from hist_exch_rates
where ex_date between ''20070101'' and ''20110601'' --adjust date accordingly
) o
pivot (max(rate_mul) for ex_date1 in (' + @cols2 + ')) as p1
pivot (max(rate_div) for ex_date2 in (' + @cols3 + ')) as p2'
exec(@strSQL)
ASKER
ASKER
declare @cols1 varchar(max)
declare @cols2 varchar(max)
declare @cols3 varchar(max)
declare @strSQL varchar(max)
set @cols1 = stuff((select distinct '], [' + ex_date + '_mul], [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols2 = stuff((select distinct '], [' + ex_date + '_mul'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @cols3 = stuff((select distinct '], [' + ex_date + '_div'
from hist_exch_rates
where ex_date between '20070101' and '20110601' --adjust date accordingly
for xml path('')
), 1, 2, '') + ']'
set @strSQL = 'select fromc, toc, ' + @cols1 +
' from (
select ex_date + ''_mul'' as ex_date1,
ex_date + ''_div'' as ex_date2,
fromc,
toc,
rate_mul,
rate_div
from hist_exch_rates
where ex_date between ''20070101'' and ''20110601'' --adjust date accordingly
and toc = ''CHF''
) o
pivot (max(rate_mul) for ex_date1 in (' + @cols2 + ')) as p1
pivot (max(rate_div) for ex_date2 in (' + @cols3 + ')) as p2'
exec(@strSQL)
ASKER
ASKER
ASKER
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
Open in new window