Dynamic SQL CrossTab Stored Procedure

First, here’s the code for the procedure:


create procedure CrossTab (@Select varchar(1000),

    @PivotCol varchar(100),

    @Summaries varchar(100),

    @GroupBy varchar(100),

    @OtherCols varchar(100) = Null)

AS
 

set nocount on

set ansi_warnings off

 

declare @Vals varchar(8000);

set @Vals = ;

 

set @OtherCols= isNull(‘, ‘ + @OtherCols,)

 

create table #temp  (Pivot varchar(100))

 

insert into #temp

exec (‘select distinct convert(varchar(100),’ + @PivotCol + ‘) as Pivot FROM (‘ + @Select + ‘) A’)

 

select @Vals = @Vals + ‘, ‘ +

    replace(replace(@Summaries,‘(‘,‘(CASE WHEN ‘ + @PivotCol + ‘=”’ +

            Pivot +  ”’ THEN ‘),‘)[‘‘ END) as [‘ + Pivot )

from #Temp
order by Pivot

 

drop table #Temp

 

exec ‘select ‘ + @GroupBy + @OtherCols + @Vals +

       ‘ from (‘ + @Select + ‘) A GROUP BY ‘ + @GroupBy)

 

set nocount off

set ansi_warnings on


And here’s a description of the parameters and how to use it:

  • Select – This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc — anything that you can cut and paste into query analyzer and execute.  (And, in fact, you should always do this when testing things out).
  • PivotCol – This is the column (or a valid expression) that you wish to pivot on.
  • Summaries – The summaries you wish to perform. Note that this is plural; you can summarize multiple columns if you wish.  See below for more information.
  • GroupBy – This is the list of the non-pivot columns you wish to group by and return, separated by commas.
  • OtherCols (optional) – Any other columns to return that you are not grouping by, also separated by commas. Make sure that this list does not contain references to columns that are not being summarized or grouped.

Here’s the format you should use when specifying the Summaries parameter:

SUM(Field ELSE DefaultValue)[Prefix], ….

First, list the aggregate function you wish to use, and within that function put the column you wish to summarize, and add an ELSE clause with what that column’s default value is. For example, put 0 if you wish to display all 0’s for empty columns, or NULL if you wish to display nulls.

Next, immediately following the aggregate function (with no spaces) put the prefix for the column name in brackets. This is what will be appended to the beginning of the column name, followed by the value from the pivot column. Leave as [] to just use the pivot column’s value as the field name.

Finally, you may have multiple summaries just seperate them by comma’s. Remember if you have many pivot values, you will probably end up with too many columns and/or a T-SQL statement > 8000 characters so you may get an error.

Examples:

If the pivot column is “Employee”, with values of 001, 002, 003 and 004:

SUM(Hours ELSE 0)[]

returns column headings of 001, 002, 003, 004, and returns a 0 in all “empty” fields.

SUM(Hours ELSE 0)[Hours], MAX(Date ELSE Null)[MaxDate]

returns column headings of Hours001, MaxDate001, Hours002, MaxDate002, Hours003, MaxDate003, ..etc …

SUM(Amount ELSE Null)[]

returns column headings of 001,002,003,004 and returns a NULL in all “empty” fields.  Note that you can use SUM(Amount)[] as a shortcut, since ELSE NULL is redundant and is the default behavior.

SUM(Amount ELSE 0)[Amount], COUNT(Amount)[Qty]

returns column headings of Amount001, Qty001, Amount002, Qty002, … etc …


Here are some samples using either Northwind or Pubs:

exec CrossTab

‘SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ‘,

‘Year(OrderDate)’,

‘Count(LastName)[]’,

‘LastName’

 

exec CrossTab

‘select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles

inner join pubs..sales sales on

(sales.title_id=titles.title_id)

inner join pubs..stores stores on sales.stor_id = stores.stor_id ‘,

‘stor_id’,

‘SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE ””)[MaxStoreName], COUNT(1 ELSE 0)[Count]’,

‘title’,

‘Count(*) as TotalCount’

This entry was posted in SQL server and tagged , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s