Dynamic Crosstab with multiple PIVOT Columns
Jeff Smith in his weblog showed how to generate Crosstab reports using a stored procedure. It works only for one PIVOT Column.I had a requirement to generate crosstab reports with more than one pivot column. So I used the same approach he used and modified his procedure as shown below
CREATE procedure CrossTab
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100) = Null
)
AS
set nocount on
set ansi_warnings off
declare @sql varchar(8000)
Select @sql = ''
Select @OtherCols= isNull(', ' + @OtherCols,'')
create table #pivot_columns (pivot_column_name varchar(100))
Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+''''
insert into #pivot_columns
exec(@sql)
select @sql=''
create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100))
Select @PivotCol=''
Select @PivotCol=min(pivot_column_name) from #pivot_columns
While @PivotCol>''
Begin
insert into #pivot_columns_data(pivot_column_name,pivot_column_data)
exec (
'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data from
('+
@select
+'
) T'
)
Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end
select @sql = @sql + ', ' +
replace(
replace(
@Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' +
pivot_column_data + ''' THEN '
),
')[', ' END) as [' + pivot_column_data
)
from #pivot_columns_data
order by pivot_column_name
exec ( 'select ' + @GroupBy +@OtherCols +@sql +
' from (
'+
@select
+'
) T
GROUP BY ' + @GroupBy)
drop table #pivot_columns
drop table #pivot_columns_data
set nocount off
set ansi_warnings on
Now execute the procedure by supplying two pivot columns shipcountry and Year(Orderdate)
EXEC CrossTab
'SELECT LastName, OrderDate,shipcountry FROM northwind..Employees Employees
INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'shipcountry,Year(OrderDate)',
'Count(LastName)[]',
'LastName'