SQL Server dynamic pivot with multiple columns -


here's scenario in. have data in following format.

my source data

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

expected output

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

Popular posts from this blog

serialization - Convert Any type in scala to Array[Byte] and back -

i have following question: i have variable value in program declared value. i want convert value byte array.. how can serialize byte array , back? found examples related other types such double or int, not any. this should need. it's pretty similar how 1 in java. import java.io.{bytearrayinputstream, bytearrayoutputstream, objectinputstream, objectoutputstream} object serialization extends app { def serialise(value: any): array[byte] = { val stream: bytearrayoutputstream = new bytearrayoutputstream() val oos = new objectoutputstream(stream) oos.writeobject(value) oos.close stream.tobytearray } def deserialise(bytes: array[byte]): = { val ois = new objectinputstream(new bytearrayinputstream(bytes)) val value = ois.readobject ois.close value } println(deserialise(serialise("my test"))) println(deserialise(serialise(list(1)))) println(deserialise(serialise(map(1 -> 2)))) println(deserialise(seri
Read more

matplotlib support failed in PyCharm on OSX -

any idea how fix ? how can debug might problem ? not seem : https://youtrack.jetbrains.com/issue/py-15520 i in console: python 2.6.9 (unknown, jul 14 2015, 19:46:31) [gcc 4.2.1 compatible apple llvm 6.0 (clang-600.0.39)] on darwin >>> import matplotlib.pyplot plt matplotlib support failed the backend : >>> import matplotlib >>> print(matplotlib.rcparams['backend']) macosx
Read more

python - Matplotlib: TypeError: 'AxesSubplot' object is not callable -

i new programming please pardon me if rookie mistake. i writing python script have defined functions create subplots of distributions. i have slider widget end user change value of variables(mu , sigma) , see effect on distribution. here's code import numpy np print np.__version__ import scipy print scipy.__version__ scipy.stats import norm, lognorm, uniform import matplotlib print matplotlib.__version__ import matplotlib.pyplot plt matplotlib.widgets import slider, button, radiobuttons, checkbuttons #####calculating mean , standard deviation##### global mu_a1 mu_a1 = 1 global mu_b1 mu_b1 = 10 global mu_c1 mu_c1 = -13 global sigma_a1 sigma_a1 = 0.14 global sigma_b1 sigma_b1 = 1.16 global sigma_c1 sigma_c1 = 2.87 mu_x01 = -11 sigma_x01 = 1.9 #####_____##### #####generating random data##### a1 = 0.75*mu_a1 + (1.25 - 0.75)*sigma_a1*np.random.sample(10000) b1 = 8*mu_b1 + (12 - 8)*sigma_b1*np.random.sample(10000) c1 = -12*mu_c1 + 2*sigma_c1*np.random.sample(10000)
Read more