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'



Published 27 August 2007 16:23 by Madhivanan

Comments

# Dynamic PIVOT in SQL Server 2005

27 August 2008 15:53 by Madhivanan

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results But it is required

# Dynamic PIVOT in SQL Server 2005

27 August 2008 16:02 by SQL Server Transact-SQL (SSQA.net)

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results Consider this

# columnar to row-based data - dBforums

10 March 2009 09:23 by columnar to row-based data - dBforums

Pingback from  columnar to row-based data - dBforums

# re: Dynamic Crosstab with multiple PIVOT Columns

19 March 2009 04:23 by veera

itemno wh1 wh2 wh3 tot

----------------------

F1     10  10   0  20

F2     10   0  10  20

F3      0   0  30  30

----------------------

i need this type of format report. please give some idea to do this report

# Multiple Rows into One Row w/ Multiple Columns - dBforums

Pingback from  Multiple Rows into One Row w/ Multiple Columns - dBforums

# How to pivot this? - dBforums

07 July 2009 11:08 by How to pivot this? - dBforums

Pingback from  How to pivot this? - dBforums

# re: Dynamic Crosstab with multiple PIVOT Columns

01 December 2009 10:36 by jtoutou

Hello....how can we use the DATENAME(MONTH,OrderDate) function instead....

# Select data horizontally - dBforums

12 September 2012 19:09 by Select data horizontally - dBforums

Pingback from  Select data horizontally - dBforums