sql server - SQL pivot table eow into columns -
i have rate change history need display in different format. i'm thinking pivot table may able me. table called all_rates , output looks this:
acc_no bal rate eff_dt 1001 10000 3.5 01/01/2006 1001 12000 4.25 01/02/2006 1002 11000 3.25 01/02/2006 1002 11500 4.75 01/05/2006 1003 35000 3.5 01/01/2006 1003 0 0 01/05/2006 i show 1 account number per row follows:
month 01/01/2006 01/02/2006 01/03/2006 01/04/2006 01/05/2006 01/06/2006 acc_no bal rate bal rate bal rate bal rate bal rate bal rate 1001 10000 3.5 12000 4.25 12000 4.25 12000 4.25 12000 4.25 12000 4.25 1002 0 0 11000 3.25 11000 3.25 11000 3.25 11500 4.75 11500 4.75 1003 35000 3.5 35000 3.5 35000 3.5 35000 3.5 0 0 0 0
you can achieve functionality using derived columns follows:
declare @monthbal varchar(max) , @monthrate varchar(max) , @query varchar(max) create table [accountdetails] ( [acc_no] int , [bal] int , [rate] decimal(18, 2) , [eff_dt] date ) create table [accountdetailsderived] ( [acc_no] int , [bal] int , [rate] decimal(18, 2) , [eff_dt_bal] varchar(50) , [eff_dt_rate] varchar(50) ) i have declared 2 variables @monthbal , @monthrate store rate , month values respectively. have created table derived columns can create while selecting main table
next insert mentioned data in main table , derived data in second table:
insert [accountdetails] ( [acc_no] , [bal] , [rate] , [eff_dt] ) values (1001, 10000, 3.5, '01/01/2006') , (1001, 12000, 4.25, '01/02/2006') , (1002, 11000, 3.25, '01/02/2006') , (1002, 11500, 4.75, '01/05/2006') , (1003, 35000, 3.5, '01/01/2006') , (1003, 0, 0, '01/05/2006') insert [accountdetailsderived] ( [acc_no] , [bal] , [rate] , [eff_dt_bal] , [eff_dt_rate] ) select [acc_no] , [bal] , [rate] , convert(varchar, [eff_dt] ) + ' bal' , convert(varchar, [eff_dt] ) + ' rate' [accountdetails] i query derived table see data required be:
select * [accountdetailsderived] i resultset as:
acc_no | bal | rate | eff_dt_bal | eff_dt_rate 1001 | 10000 | 3.50 | 2006-01-01 bal | 2006-01-01 rate 1001 | 12000 | 4.25 | 2006-01-02 bal | 2006-01-02 rate 1002 | 11000 | 3.25 | 2006-01-02 bal | 2006-01-02 rate 1002 | 11500 | 4.75 | 2006-01-05 bal | 2006-01-05 rate 1003 | 35000 | 3.50 | 2006-01-01 bal | 2006-01-01 rate 1003 | 0 | 0.00 | 2006-01-05 bal | 2006-01-05 rate now take distinct months variables separately follows:
select @monthbal = stuff((select distinct ',[' + convert(varchar,[eff_dt_bal]) + ']' [accountdetailsderived] xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') select @monthrate = stuff((select distinct ',[' + convert(varchar,[eff_dt_rate]) + ']' [accountdetailsderived] xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') i fill query variables 2 pivots 1 each month pattern:
set @query = 'select [acc_no],' + @monthbal + ', '+ @monthrate +' [accountdetailsderived] pivot ( max([bal]) [eff_dt_bal] in (' + @monthbal + ') ) p pivot ( max([rate]) [eff_dt_rate] in (' + @monthrate + ') ) p2 ' now when execute query desired resultset (the nulls can handled further have not done)
exec(@query) the resultset:
acc_no | 2006-01-01 bal | 2006-01-02 bal | 2006-01-05 bal | 2006-01-01 rate | 2006-01-02 rate | 2006-01-05 rate 1001 | null | 12000 | null | null | 4.25 | null 1001 | 10000 | null | null | 3.50 | null | null 1002 | null | null | 11500 | null | null | 4.75 1002 | null | 11000 | null | null | 3.25 | null 1003 | null | null | 0 | null | null | 0.00 1003 | 35000 | null | null | 3.50 | null | null the whole script as:
begin tran declare @monthbal varchar(max) , @monthrate varchar(max) , @query varchar(max) create table [accountdetails] ( [acc_no] int , [bal] int , [rate] decimal(18, 2) , [eff_dt] date ) create table [accountdetailsderived] ( [acc_no] int , [bal] int , [rate] decimal(18, 2) , [eff_dt_bal] varchar(50) , [eff_dt_rate] varchar(50) ) insert [accountdetails] ( [acc_no] , [bal] , [rate] , [eff_dt] ) values (1001, 10000, 3.5, '01/01/2006') , (1001, 12000, 4.25, '01/02/2006') , (1002, 11000, 3.25, '01/02/2006') , (1002, 11500, 4.75, '01/05/2006') , (1003, 35000, 3.5, '01/01/2006') , (1003, 0, 0, '01/05/2006') insert [accountdetailsderived] ( [acc_no] , [bal] , [rate] , [eff_dt_bal] , [eff_dt_rate] ) select [acc_no] , [bal] , [rate] , convert(varchar, [eff_dt] ) + ' bal' , convert(varchar, [eff_dt] ) + ' rate' [accountdetails] select * [accountdetailsderived] select @monthbal = stuff((select distinct ',[' + convert(varchar,[eff_dt_bal]) + ']' [accountdetailsderived] xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') select @monthrate = stuff((select distinct ',[' + convert(varchar,[eff_dt_rate]) + ']' [accountdetailsderived] xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') print @monthbal print @monthrate set @query = 'select [acc_no],' + @monthbal + ', '+ @monthrate +' [accountdetailsderived] pivot ( max([bal]) [eff_dt_bal] in (' + @monthbal + ') ) p pivot ( max([rate]) [eff_dt_rate] in (' + @monthrate + ') ) p2 ' print(@query) exec(@query) rollback thats it!
Comments
Post a Comment