SQL Server dynamic pivot with multiple columns -
here's scenario in. have data in following format.
issuedon country sales transactions ------------------------------------------ 29-aug-16 india 40 8 29-aug-16 australia 15 3 29-aug-16 canada 15 3 30-aug-16 india 50 10 30-aug-16 australia 25 5 30-aug-16 canada 10 2 31-aug-16 india 100 25 31-aug-16 australia 30 10 31-aug-16 canada 55 12 this output looking for
issueddate australia canada india totalsales transactionscount --------------------------------------------------------------------- 29-aug-16 15 15 40 70 14 30-aug-16 25 10 50 85 17 31-aug-16 30 55 100 185 47 i have been able pivot data on country , "total sales" column. however, not able "total transactions" column right.
here's code generate source data table. if can guide me.
create table tbl1 ( issuedon date, country varchar(100), sales bigint, transactions bigint ) insert tbl1(issuedon, country, sales, transactions) values ('2016-08-29', 'india', 40, 8), ('2016-08-29', 'australia', 15, 3), ('2016-08-29', 'canada', 15, 3), ('2016-08-30', 'india', 50, 10), ('2016-08-30', 'australia', 25, 5), ('2016-08-30', 'canada', 10, 2), ('2016-08-31', 'india', 100, 25), ('2016-08-31', 'australia', 30, 10), ('2016-08-31', 'canada', 55, 12) select * tbl1
the following stored procedure used bulk of dynamic pivots
exec [prc-pivot] 'tbl1','country','sum(sales)[]','issuedon','sum(transactions)[transactions],sum(sales)[totalsales]' issuedon transactions totalsales australia canada india 2016-08-29 14 70 15 15 40 2016-08-30 17 85 25 10 50 2016-08-31 47 185 30 55 100 the stored procedure
alter procedure [dbo].[prc-pivot] ( @source varchar(1000), -- table or select statement @pvotcol varchar(250), -- field name or expression ie. month(date) @summaries varchar(250), -- aggfunction(aggvalue)[optionaltitle] @groupby varchar(250), -- optional additional group @othercols varchar(500) ) -- optional group or aggregates --exec [prc-pivot] 'select year=year(tr_date),* [chinrus-series].[dbo].[ds_treasury_rates]','''q''+datename(qq,tr_date)','avg(tr_y10)[-avg]','year','count(*)[records],min(tr_y10)[min],max(tr_y10)[max],avg(tr_y10)[avg]' --exec [prc-pivot] '#temp','attribute','max(description)[]','id','count(*)[records]' set nocount on declare @vals varchar(max),@sql varchar(max); set @vals = '' set @othercols= isnull(', ' + @othercols,'') set @source = case when @source 'select%' @source else 'select * '+@source end create table #temppvot (pvot varchar(100)) insert #temppvot exec ('select distinct convert(varchar(100),' + @pvotcol + ') pvot (' + @source + ') a') --select @vals = @vals + ', isnull(' + replace(replace(@summaries,'(','(case when ' + @pvotcol + '=''' + pvot + ''' '),')[', ' end),null) [' + pvot ) #temppvot order pvot select @vals = @vals + ', isnull(' + replace(replace(@summaries,'(','(case when ' + @pvotcol + '=''' + pvot + ''' '),')[', ' end),0) [' + pvot ) #temppvot order pvot drop table #temppvot set @sql = replace('select ' + isnull(@groupby,'') + @othercols + @vals + ' (' + @source + ') pvtfinal ' + case when isnull(@groupby,'')<>'' 'group ' + @groupby + ' order ' + @groupby else '' end,'select , ','select ') --print @sql exec (@sql) 
Comments
Post a Comment