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_nameexec
(
'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 onNow 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'