October 9, 2010 at 1:45 am
Hi,
I am in a need of help for pivot query generation for multiple aggregation with varying columns. Consider the following query and its output.
Select distinct Column2 as 'Name', Column20 as 'Year',
SUM(Column35) as 'Payroll', SUM(Column33) as 'Income',
SUM(Column52) as 'Billed' from Level_Zero Group By Column20, Column2 Order By Column2
Resultset of above query:
Name YearPayrollIncomeTotal Billed
School12009315464157872950303
School12010159297110423499522
School22009190410-213968324112
School22010140433-98061298334
School32009389233149613916595
School3201020026497166493687
For this result set i need to generate a query using pivot clause or cross tabs to display in the below format
Name [2009 Payroll][2009 Income][2009 Total Billed][2010 Payroll][2010 Income][2010 Total Billed]
School1315464157872950303159297110423499522
School2190410-213968324112140433-98061298334
School338923314961391659520026497166493687
I was able to bring up for static columns. But in my case all the columns like name, year and expressions like payroll, income gets varied based on the user selection. Based on the columns and expressions provided by user i need to frame query to get result set in the above way by using some stored procedure or something. Can some one guide me in achieving this? Also please let me know whether this can be generated using pivot clause or cross tabs since my table has large amount of datas. Any help would be much appreciated. Thanks in Advance.
Regards,
priya
October 9, 2010 at 7:26 am
WITH CTE AS
(
Select distinct
Name = Column2 ,
[Year] = Column20 ,
Payroll = SUM(Column35),
Income = SUM(Column33),
Billed = SUM(Column52)
from Level_Zero
Group By Column20, Column2
)
SELECT Name,
[2009 Payroll] = max(CASE WHEN [Year] = 2009 THEN Payroll ELSE NULL END),
[2009 Income] = max(CASE WHEN [Year] = 2009 THEN Income ELSE NULL END),
[2009 Billed] = max(CASE WHEN [Year] = 2009 THEN Billed ELSE NULL END),
[2010 Payroll] = max(CASE WHEN [Year] = 2010 THEN Payroll ELSE NULL END),
[2010 Income] = max(CASE WHEN [Year] = 2010 THEN Income ELSE NULL END),
[2010 Billed] = max(CASE WHEN [Year] = 2010 THEN Billed ELSE NULL END)
FROM CTE
GROUP BY Name
Order By Name;
For the dynamic portion (basing it off of the user's input), please read the Cross-Tabs and Pivot Tables, Part 1 and Part 2 article links in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 9, 2010 at 8:05 am
Thanks Wayne! It was very helpful and am able to create dynamically.
October 12, 2010 at 2:14 am
Hi Wayne,
I am able to create cross tab query for dynamic columns based on user input. But I face some issues in the expressions provided by user. Following is the stored procedure that i have generated..
declare @column varchar(100), @pivot varchar(100), @table varchar(100),
@expr1 varchar(max), @expr2 varchar(max), @expr3 varchar(max)
declare @sql varchar(max), @pivottable varchar(max), @pivotqry varchar(max),
@pivotexpr varchar(max), @pivotcol1 varchar(max), @pivotcol2 varchar(max), @pivotcol3 varchar(max),
@name1 varchar(2000), @name2 varchar(2000), @name3 varchar(2000),
@exprcolumn1 varchar(max), @exprcolumn2 varchar(max), @exprcolumn3 varchar(max) , @datatype varchar(max), @delim varchar(10)
set @column = 'column2'
set @pivot = 'column20'
set @pivot = 'column20'
set @expr1 = 'sum(column35)'
set @expr2 = 'SUM(CASE level_zero.column20 WHEN 2010 THEN level_zero.column35 ELSE NULL END)'
set @expr3 = 'SUM(column66)'
set @table = 'level_zero'
set @exprcolumn1 = 'Payroll'
set @exprcolumn2 = 'Op Income'
set @exprcolumn3 = 'Total Billed'
set @pivotexpr = ''
set @delim = ''
select @datatype = (select name from sys.types where system_type_id in(select system_type_id from sys.columns where object_id =(select object_id from sys.tables where name = 'level_zero') and name = 'column81') )
if(@datatype = 'varchar' or @datatype = 'nvarchar' or @datatype = 'datetime')
begin
set @delim = ''''''
--set @delim = ''
end
if (@expr1 is not null or @expr1 <> '')
begin
set @pivotexpr = @pivotexpr + ' ''' + SUBSTRING(@expr1, 0, charindex( '(', @expr1 )+1) + ' case ' + convert(varchar(100),@pivot ) + ' when ' + '''+''' + @delim + ''' + ' +
'convert(varchar(100),' + @pivot + ' ) + ''''' + @delim + ' + '' then ' + SUBSTRING(@expr1, charindex( '(', @expr1 )+1 , charindex( ')', @expr1 ) -charindex( '(', @expr1 ) -1)
+ ' else null end) as ''' + ' as ''pivot1'', ' + '''['' + ' + ' convert(varchar(100),' + @pivot + ') '+ ' + '' ' + @exprcolumn1 + ''' + ''],'' as ''name1'','
end
if (@expr2 is not null or @expr2 <> '')
begin
set @pivotexpr = @pivotexpr + ' ''' + SUBSTRING(@expr2, 0, charindex( '(', @expr2 )+1) + ' case ' + convert(varchar(100),@pivot ) + ' when ' + '''+''' + @delim + ''' + ' +
'convert(varchar(100),' + @pivot + ' ) + ''''' + @delim + ' + '' then ' + SUBSTRING(@expr2, charindex( '(', @expr2 )+1 , charindex( ')', @expr2 ) -charindex( '(', @expr2 ) -1) +
' else null end) as ''' + ' as ''pivot2'', ' +''' ['' + ' + ' convert(varchar(100),' + @pivot + ') '+ ' + '' ' + @exprcolumn2 + ''' + ''],'' as ''name2'','
end
if (@expr3 is not null or @expr3 <> '')
begin
set @pivotexpr = @pivotexpr + ' ''' + SUBSTRING(@expr3, 0, charindex( '(', @expr3 )+1) + ' case ' + convert(varchar(100),@pivot ) + ' when ' + '''+''' + @delim + ''' + ' +
'convert(varchar(100),' + @pivot + ' ) + ''''' + @delim + ' + '' then ' + SUBSTRING(@expr3, charindex( '(', @expr3 )+1 , charindex( ')', @expr3 ) -charindex( '(', @expr3 ) -1) +
' else null end) as ''' + ' as ''pivot3'', ' + +''' ['' + ' + ' convert(varchar(100),' + @pivot + ') '+ ' + '' ' + @exprcolumn3 + ''' + ''],'' as ''name3'','
end
print @pivotexpr
set @pivottable = 'select distinct '+ substring(@pivotexpr, 0, len(@pivotexpr)) + ' into ##pvttable3 ' + ' from ' + @table + ' where ' + @pivot + ' is not null ' --order by ' + @pivot
exec(@pivottable)
print @pivottable
set @pivotqry = ''
select * from ##pvttable3
--select * from ##pvttable3
declare cur cursor for select [pivot1 ], [name1],[pivot2 ], [name2],[pivot3 ], [name3] from ##pvttable3
open cur
fetch next from cur into @pivotcol1, @name1, @pivotcol2, @name2, @pivotcol3, @name3
while (@@FETCH_STATUS = 0)
begin
set @pivotqry = @pivotqry + @pivotcol1 + @name1 + @pivotcol2 + @name2 + @pivotcol3 + @name3
fetch next from cur into @pivotcol1, @name1, @pivotcol2, @name2, @pivotcol3, @name3
end
close cur
deallocate cur
--print @pivotqry
--print 'select distinct ' + @column + ' , ' + substring(@pivotqry, 0, len(@pivotqry )) + ' from ' + @table + ' group by ' + @column + ' order by ' + @column
set @pivotqry = 'select distinct ' + @column + ' , ' + substring(@pivotqry, 0, len(@pivotqry )) + ' from ' + @table + ' group by ' + @column + ' order by ' + @column
exec (@pivotqry)
drop table ##pvttable3
In this stored procedure, i have got expressions(@expr1 , @expr2, @expr3) as inputs where user can provide any expressions. This stored procedure can handle some sample expressions as in @expr3. But it fails for @expr2, since it has got convert statement and all. Is there a way to implement it so that it can handle any complex expression. Please provide me some idea of doing it.
Thanks for any help!!
Regards,
priya
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply