Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Another Dynamic SQL CrossTab Stored Procedure

First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!  This entire concept and pretty much all of the ideas I've had regarding this topic and this techinique in general are all due to Rob's great work and his very clever stored procedure.

It must be crosstab season or something, because lately I've been getting quite a few emails and comments about an alternative stored procedure that I've posted in the comments to that article that has been helping quite a few users.   To potentially help others out there with this common request (which I still feel should be mostly done at the presentation layer, but I suppose it's not always possible) here's a quick recap/reprint of my adaptation of Rob's excellent idea and some notes.

The main difference between Rob's original stored procedure and mine are that

  • you can summarize multiple values
  • it only creates columns based on your actual select statement ( not based on all rows in a table)
  • it works fine for multi-users (no global temp tables)
  • it allows you to easily specify default values for your pivots

Note that if you read the comments from the article, you'll see lots of modifications and adaptations of Rob's original that do address some of these issues, but I am proud of the brevity and flexibility of my code and I feel that it works well and is easily modified (see the end of this article for one idea).  Depending on your needs, it may be useful to incorporate some of the other ideas presented in the article and the comments, so be sure to do some research if necessary or if you are interested in learning more.

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'

 


 

Modification Ideas

A common problem with these procedures is when there are too many columns and the SQL statement stored in the variable ends up with a length of more than 8000 characters, causing truncation problems and syntax errors.

Click here for a modification of the stored procedure which allows you to extend the length of the SQL that can be generated fairly easily that seems to work well:


see also:

Print | posted on Monday, May 02, 2005 2:00 PM | Filed Under [ T-SQL CrossTabs / Pivoting Data Code Library - SQL ]

Feedback

Gravatar

# re: More Dynamic Cross Tabs using T-SQL

Just wanted to mention: That was brilliant!
iow
exec CrossTab 'select 1 as i,''That'' as txt union all select 2,''was'' union all select 3,''brilliant!''','i','MAX(txt ELSE '''')[]','i'
5/2/2005 6:40 PM | ClaesW(rockmoose)
Gravatar

# re: More Dynamic Cross Tabs using T-SQL

Damn Jeff, that is a work of art! And it's simple too! Much cleaner and better than the original.

That one's definitely going into the server at work. Very nice job!

And thanks for the kind words, glad that I got to inspire someone. :)
5/2/2005 7:47 PM | robvolk
Gravatar

# re: More Dynamic Cross Tabs using T-SQL

Nicely done. Very well played!
5/4/2005 10:54 AM | Amethystium
Gravatar

# re: More Dynamic CrossTabs using T-SQL

What if I had a pivot table... but instead of some
SUM() or COUNT() appearing in the columns.. I wanted
the columns to contain fields.

"Dates" would be listed down the left edge of the grid.
"Times" would be listed across the top of the grid.
And the cells would contain the fields found for that date/time.

Instead of "4" (matches) found for that date/time.
It would say "Jim, John, Paul, Sam".

The whole grid would be filled with fields.

5/29/2005 12:53 AM | Carol
Gravatar

# re: More Dynamic CrossTabs using T-SQL

It is not working when i have a where clause...

i think the single quotes are confliting...how can i avoid this...

i have some thing like this..

Exec CrossTab
'SELECT CustomerType, Sales FROM myTable WHERE CustomerType='V' ','CustomerType','Count(Sales)[],'CustomerType'

everything works fine if i remove the WHERE clause...

8/3/2005 10:31 AM | DotNetGnat
Gravatar

# re: More Dynamic CrossTabs using T-SQL

ok i figured the problem..you need two single quotes instead of one single quote
8/3/2005 11:00 AM | DotNetGnat
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I have similar problem as Carol...
I have the following table:

Name Date Description
abc 1st day
abc 2nd night
abc 3rd both
def 1st both
def 2nd day
def 3rd night

To be transposed as:

Name 1st 2nd 3rd
abc day night both
def both day night

Can it be done using the proc?
8/16/2005 6:00 AM | Anita
Gravatar

# re: More Dynamic CrossTabs using T-SQL

If you want to include non-numeric values in a pivot column, use max() or min() as the summary expression. Max and Min work on all data types except uniqueidentifier, text and image.
8/17/2005 5:50 PM | robvolk
Gravatar

# re: More Dynamic CrossTabs using T-SQL

For SQLEXPRESS or SQL SERVER 2005

replace all "PIVOT" to "TPIVOT" , pivot is a reserviert word :)

Coool...
8/25/2005 7:18 AM | Juver Paredes Pajar
Gravatar

# re: More Dynamic CrossTabs using T-SQL

how can i create dynamic result set into table or view ?
8/31/2005 11:14 PM | riang
Gravatar

# Paladin Consulting

9/13/2005 9:28 AM | Welcome to Jeff's Blog
Gravatar

# Paladin Consulting

9/13/2005 12:26 PM | Welcome to Jeff's Blog
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Thanks to you and Rob Volk. Thanks to you for putting it here - easier than sifting through 9 pages of posts on SQLTeam (but worth reading through to learn).

One suggestion - this still dies if there is a single tick in the data. I think making this change resolves the problem (did in my case):

...
select @tmp = replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + REPLACE(Pivot,'''','''''') +
...

Just replacing single tick w/ two singles so the corresponding execute won't error.

Thanks again - Joe
9/16/2005 11:28 AM | Joe K
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Good call, Joe ... the modified version of this proc that handles much larger sql statements( see the link at the bottom of the post) incorporates this feature, but I had forgotten to add that fix to the original stored proc as well.

As you mention, if any of the data in the pivot columns has any single quotes (i.e., like "O'Brien") then you will need to make the change that Joe suggests to general valid sql.

Thanks!
- Jeff
9/16/2005 11:55 AM | Jeff S
Gravatar

# re: More Dynamic CrossTabs using T-SQL

This code works great, but going back to the comment made on 8/31/2005 by riang, is there a way to insert the crosstab result sets into tables?

Thanks.

Chris
10/14/2005 1:57 PM | dejavuaz
Gravatar

# re: More Dynamic CrossTabs using T-SQL

This is sweet! We had a cross tab sproc in house that worked, but it was 4 times as big and 1/2 as useful. Thanks, Jeff, I am so thrilled I could kiss you (and I'm happily married!). :)

One thing that I changed. The following line:

@Vals = @Vals + ', ' + REPLACE ... + TPivot )

I wrapped with ISNULL( <expr>, '')

This allows me to have NULLs in my pivot column values, for instance if they are coming from a LEFT OUTER JOIN. In this case it simply ignores those values instead of returning no pivot columns.

Thanks again!

Jerry

10/19/2005 9:21 AM | Jerry Foster
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Great !! It made my job easier. One question.
If I use the following expression in my groupby clause, I am not able to set the column name, hence I get the result set with "No Column Name".

SUBSTRING(DATENAME(dw, MeasureDate), 1, 3)
10/22/2005 6:12 PM | Anuradha
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Another question....How to use the order by clause? I used the order by in the select statement but, I did not get the expected results? Can we sort the final output using the input order by clause columns?

Thanks a bunch !!
10/22/2005 11:06 PM | Anuradha
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Is it possible to do a total summation at the very bottom of each pivot column?
1/10/2006 12:28 PM | RLN
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Regarding whether you can do a total summation, the answer is yes, you can do it using the WITH ROLLUP option of the GROUP BY clause. You can modify Jeff's code to include an extra parameter:

...@OtherCols varchar(100) = Null),
@rollup bit=0
AS ...

And add it like this:

declare @roll varchar(20)
set @roll=CASE @rollup WHEN 1 THEN ' WITH ROLLUP' ELSE '' END
exec ( 'select ' + @GroupBy + @OtherCols + @Vals +
' from (' + @Select + ') A GROUP BY ' + @GroupBy + @roll)

Books Online has more details about ROLLUP, also check out CUBE.
2/10/2006 8:19 AM | robvolk
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Jeff,
This thing has been working sweet -- until today! I'm trying to add a 4th summary column to the query and it's failing.

At the end of the proc, instead of executing the dynamic SQL statement, for testing I print it to the screen. What I'm finding is that the 1st, 2nd and 3rd summary case statements for each pivot value print fine, but instead of printing the 4th summary item, it dumps a comma in it's place. So instead of 1 comma following the 3rd summary item, there's 2 and at the very end of the select, it puts a comma between the last item and 'From'.

Any ideas? I've also tried this with the modified version just in case the SQL Statement exceeded 8000 chars.

Thanks,

Bob
2/16/2006 7:15 PM | Bob
Gravatar

# re: More Dynamic CrossTabs using T-SQL

This is kind of like what Anuradha asked on 10/22/2005: In one of the reports that use this SP, my pivot column is numeric (ie month numbers), so the crosstab ends up with the columns x,y,1,10,11,12,2,3,4,5,6,7,8,9 instead of x,y,1,2,3,4,5,6,7,8,9,10,11,12.

Presumably this is because the Pivot column on the Temp table is declared as varchar(100). Is there any way around this?

Thanks,
B
2/23/2006 12:12 PM | BrianQ
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I'm going to chime in with riang (8/31/2005) and dejavuaz (10/14/2005), and ask "Is there a way to insert the results of the crosstab into a table (be it real or temp)?"

-Joel
2/25/2006 6:39 PM | Joel
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Hello? Is anyone here?

-Joel
3/1/2006 10:58 AM | Joel
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I didn't understand
3/7/2006 3:31 PM | S
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Joel -- Can you give me an example of why would you want to do this? It's bad enough returning a resultset with unknown column names, why would you then want to put this into a table? What can you do with this table w/o knowing the column names ahead of time? It goes against everything that a relational database is about.

Some other posts on my blog show how much easier and more efficient it is to do the cross-tabbing in other places, rather than at the database.

Overall, I am glad that this code helps some people and maybe gives them some ideas, but doing crosstabs like this in the database really isn't the way to go.
3/7/2006 3:54 PM | Jeff
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Hi,

Is it possible to add a Total column which shows the total of all values in a row.

e.g

Region Jan Feb Mar Total(Jan+Feb+Mar)


Thanks
Shafiq
3/13/2006 11:13 AM | Shafiq
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I have appx. a 200,000 records that pivot into some 25,000 records. I have been pivoting this in Access and you can imagine the time it takes. So this is really great. I want to thank you.

The results are perfect but because there are so many records, I am trying to get them into a local table, to produce reports from. These are the 2 lines I have added to the crosstab just above the exec.

truncate table tLocalTable

insert into tLocalTable
(fkCourseSection, Term, Div, Subject, RateGroup, TitleType, SubTitle, FacID, FacultyName, LinkField,
QuesNum, Question, SurveyCnt, TotSurveyCnt, pct0, pct1, pct2, pct3, pct4, [AVG])

exec ( 'select ' + @GroupBy + @OtherCols + @Vals + ' from (' + @Select + ') A GROUP BY ' + @GroupBy)


The crosstab compiles fine.

When I run the exec crosstab:

exec CrossTab
'select * from vw_ScannedResults',
'QuesAnswer',
'Count(PID)[]',
'Term, fkCourseSection, Division, Subject, RateGroup, TitleType, SubTitle, FacID, FacultyName, QuesNum, Question',
'sum(SurveyCount) as SurveyCnt, sum(TotSurveyCount) as TotSurveyCnt'

I receive:
Server: Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Can you tell me what I am doing wrong? Or do you already have an example using Pubs?

Thanks Judie







3/13/2006 4:36 PM | Judie
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Now that I've got the thing to run on the SQL server I need to get the thing to run on a web site. My problem is that on my ASP pages i have to call each field individually and then loop through the record set but because the Table is dynamic I don't always know the field names. Anyone got any ideas?
3/17/2006 1:55 PM | eaglescout101
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Did you see this:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/15/5175.aspx

3/17/2006 2:01 PM | Jeff
Gravatar

# Beginner questions frequently asked... and answered...

3/25/2006 6:30 PM | I want some Moore!
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Hi Jeff,

I am a beginner with SQL Server and I am still having issues trying to set up a cross tab using Pivot. I am using SQL Server 2005 at work and what I am trying to do is create a report of survey answers by users. I am running a query that generates a report but it has rows of info ordered by last name first name then the question order. Here is the outline of the query result with the column headers.

Industry, Survey ID,UserID, Last Name, First Name, Question Order, Question, Answer

I wanted to know how would I modify my query so that it shows each question at the top so it looks like this.

Last Name, First Name, Industry Question#1, Question#2

The answers would show underneath the question header and I would have it order by last name, first name.

Here is my original query.

SELECT

dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated

,dbo.Reporting_SurveyAnswers.questionid AS QuestionID

,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID

,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

,dbo.Reporting_SurveyAnswers.userid AS UserID

,dbo.Reporting_User.LastName1 AS LastName

,dbo.Reporting_User.FirstName AS FirstName

,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


FROM

dbo.Reporting_Surveys

INNER JOIN dbo.Reporting_SurveyQuestions

ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid

INNER JOIN dbo.Reporting_SurveyAnswers

ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID

INNER JOIN dbo.Reporting_User

ON dbo.Reporting_SurveyAnswers.userid = dbo.Reporting_User.userid

WHERE

dbo.Reporting_SurveyAnswers.surveyid = 1

Order by dbo.Reporting_SurveyAnswers.DateCreated

,dbo.Reporting_SurveyQuestions.ordernumber

I just need to get help starting the query.

Thanks

Michael
4/3/2006 6:24 PM | Michael
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Thanks Jeff, nice.
It seems to be doing suffixes for the resulting pivot column names instead of prefixes, for prefixes I did this mod:
replace(
replace(
replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' +Pivot + ''' THEN '),
')[', ' END) as [')
,']', Pivot+']'
)
Cheers,
-Mat
4/6/2006 9:59 AM | Matthew Hobbs
Gravatar

# re: More Dynamic CrossTabs using T-SQL

This is excellent. thank you. I added a few updates to inlcude others comments.

1. includes an order by feature
2. includes the rollup feature
3. changed the name "pivot" to thisPivot for reserved word issues

the proc:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CrossTab2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CrossTab2]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure CrossTab2 (
@SQL varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherFields varchar(100) = '',
@OrderByFields varchar(100) = '',
@rollup bit = 0,
@Debug bit = 0)
AS

set nocount on
set ansi_warnings off

declare @Vals varchar(8000);
declare @Vals2 varchar(8000);
declare @Vals3 varchar(8000);
declare @tmp varchar(1000);
declare @TotalLen int;
declare @roll varchar(20);

set @Vals = '';
set @Vals2 = '';
set @Vals3 = '';
set @TotalLen = len(@SQL) + len(@GroupBy) + Len(@OtherFields) + Len(@OrderByFields)

if (@OtherFields <> '')
begin
set @OtherFields = ', ' + @OtherFields
end

if (@OrderByFields <> '')
begin
set @OrderByFields = ' order by ' + @OrderByFields
end

set @roll = CASE @rollup WHEN 1 THEN ' WITH ROLLUP' ELSE '' END

create table #temp (thisPivot varchar(100))

insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as thisPivot FROM (' + @SQL + ') A')
select @tmp =
replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(thisPivot,'''','''''') +
''' THEN '),')[', ' END) as [' + thisPivot ),
@TotalLen = @TotalLen + Len(@tmp),
@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,
@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,
@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end
from
#Temp
order by
thisPivot

drop table #Temp

if (@Debug=0)
exec( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 +
' from (' + @SQL + ') A GROUP BY ' + @GroupBy + @roll + @OrderByFields)
else
begin
print( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 + @Vals3 +
' from (' + @SQL + ') A GROUP BY ' + @GroupBy + @roll + @OrderByFields)
end

set nocount off
set ansi_warnings on

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

the examples from above:

exec CrossTab2
'SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'Count(LastName)[]',
'LastName',
'', -- other columns
'2', -- the column number or name... number is more dynamic
1 -- with rollup

exec CrossTab2
'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',
'2', -- order by
1 -- with rollup
4/6/2006 2:00 PM | Shane McMurray
Gravatar

# re: More Dynamic CrossTabs using T-SQL

What if the table you want to use is a temporary table? How can that be done?
4/10/2006 2:47 PM | Ro
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I created the "crosstab" stored procedure. It works fine when executed from the SQL Query Analyzer. The code I am running is :
***********
exec crosstab 'SELECT top 100 percent deviceID, [year], [to] FROM prices_to order by deviceId, [year]', '[year]', 'Sum ([to])[]', 'deviceID'
***********
It comes back with 1425 rows and the data is correct.

How can I call this from the ASP code? I tried to write a simple query using the standard "ADODB.Recordset" and related commands :
************
var rsPgmMonths = Server.CreateObject("ADODB.Recordset");
rsPgmMonths.ActiveConnection = MM_autosbu_STRING;
rsPgmMonths.Source = "exec crosstab 'SELECT top 100 percent deviceID, [year], [to] FROM prices_to order by deviceId, [year]', '[year]', 'Sum ([to])[]', 'deviceID'";
rsPgmMonths.CursorType = 3;
rsPgmMonths.Open();
************
when I did "<%=rsPgmMonths.Fields.Count%>", it prints 17 which is correct; there are 17 columns in the result. However, when I did:
************
<%
for (var i=1;i<=rsPgmMonths.Fields.Count;i++)
Response.write ("<th>" + rsPgmMonths.Fields[i].name + "</th>")
%>
************
to create the header row of the table, it comes back with following error:
************
'rsPgmMonths.Fields[...].name' is null or not an object
************

Any help is greatly appreciated.
4/14/2006 11:35 AM | Paresh
Gravatar

# re: More Dynamic CrossTabs using T-SQL

I'm converting an Access DB to SQL Server. In Access, there are quite a few queries which are defined using TRANSFORM/PIVOT and these queries are in turn used in the ASP code.

How can this "CrossTab" stored procedure or it's modified version "CrossTab2" help in this situation.

Thanks!
4/14/2006 11:41 AM | Paresh
Gravatar

# re: More Dynamic CrossTabs using T-SQL

The fields collection starts from 0 and ends at length-1.

For both of you, I suggest that you read *all* of the articles on this site regarding cross tabs. Doing crosstabs in SQL Server is usually not the way to go, it is much better to have your presentation code do the cross tab.
4/14/2006 11:52 AM | Jeff
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Jeff,

Thanks for your reply. Both Qs from "Paresh" are from the same person. Even when I tweaked the FOR loop as "for (var i=0;i<rsPgmMonths.Fields.Count;i++)", the same error continues. Looks like the "rsPgmMonths.Open();" statement is not returning any data. Am I missing anything in the code above?

Thanks,
-Paresh
4/14/2006 12:07 PM | Paresh
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Hi
I'm just trying to get the original crosstab working - and it does, but when I try to use something alittle different, ie perform a long calculation in the crosstab value, then it fails.

I'm wanting to calculate a percentage based. all fields are available.

eg= 100*sum(this&that)/sum(that&this)

each individual SUM statement works seperately so I know that the syntax for each is correct, but put them together and it fails.

Thanks.


CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
7/20/2006 10:15 AM | BigJohnson
Gravatar

# re: More Dynamic CrossTabs using T-SQL

hi..gotta reiterate Carol and Anita's probs..I don't think it was resolved..Robvolk's suggestion did not work fine on my function...

how am i gonna populate my data on summaries if they aren't on datatype int?..is it possible?..like if i have gradevalues such as A, B, D..etc to populate onto the pivoted columns semesters 1, 2, adn so on..

Rob said min and max works out fine on almost all expressions except for in text, image or uniqueidentifiers..mine is in varchar thus it should..but anyway it returns this error..

"Syntax error converting the varchar value 'D' to a column of data type int."

8/7/2006 3:07 AM | Kristine
Gravatar

# Insert Results in New Table

I noticed a few guys asking about inserting the results of the query into a new table. Some guys asked why one would want to do the that.

In my case its for reporting purposes. This allows me to do less in the reporting application and thus keep my vendor lock-in to the reporting to a minimum. I managed to get Rob's original version of the sp to work and here is the code.

NOTE: a variable @newtable has been added and this will be the name of the new table

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@newtable varchar(100),
@where varchar(1000)='1=1'
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @where + ' AND ' + @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ' INTO ' + @newtable + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON

I will now have a look to see if I can achieve the same with Jeff's sp
8/10/2006 9:43 AM | Mpumelelo Msimanga
Gravatar

# re: More Dynamic CrossTabs using T-SQL

Hi Mpumelelo Msimanga ..

can u provide an example for the parameters?..i appreciate the modification u did..it is truly a useful function, only i am not sure how go around it..lol..

hmm, i did use this pivot function and wanted to utilize the modification u did..i wish to add an extended column for the totals...does the @newtable mean i have to create another table other than the one i used for my previous table?..what should i provide for the parameters of the @where part?..

thanx..
8/14/2006 10:57 PM | Kristine
Gravatar

# re: More Dynamic CrossTabs using T-SQL

regarding using the output of a sproc in a table, you could edit the procedure to select INTO a ##temp table, so that the next stored procedure can pick it up from there.
8/28/2006 8:14 PM | Shaun, Seattle
Gravatar

# re: More Dynamic CrossTabs using T-SQL

If I would have to put the result of my storedproc (crosstab) in a temporary table, what will be the structure of my table then?..i mean the columns that am gonna declare since part of which has already been pivoted?..
8/29/2006 3:44 AM | kristine
Gravatar

# re: More Dynamic CrossTabs using T-SQL

MY column headings are dates. How can I strip the time off ? Only the date is stored in my SQL table.

Also, how do I get my results into a datagrid on a web page. VB if possible please not C# !!

Thanks very much.
9/12/2006 10:36 AM | Pinto
Gravatar

# re: More Dynamic CrossTabs using T-SQL

The crosstab proc works great. But I have question. I have created a crosstab piviting on Type, that results in:

exec crosstab2
'SELECT District, Id_Nbr, Case when IsNull(Typep, '''') = '''' Then ''Type1'' Else Type END as Type FROM vwDistrictTypes',
'Type',
'Count(Id_Nbr)[] ',
'District',
'Count(Id_Nbr) as Total'


Dist Total Type1 Type2 Type3
1 485 9 401 75
2 597 7 491 99
3 686 12 573 101
4 364 4 331 29


What I need is a percentage column for each Type (pivoted column) such as:

Dist Total Type1 % Type2 % Type3 %
1 485 9 1.9 401 82.7 75 15.5
2 597 7 1.2 491 82.2 99 16.6
3 686 12 1.7 573 83.5 101 14.7
4 364 4 1.1 331 90.9 29 8.0

Is this possible

Thanks in advance.
5/9/2007 1:32 PM | JC
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

Its Good ... But I m expecting more examples...
7/30/2007 4:10 AM | Thani
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

To the person who wondering how to do web pages with a dynamic table, this approach worked for me and was a much simpler pivot approach also
http://weblogs.sqlteam.com/jeffs/articles/5174.aspx

Christie Mason
10/12/2007 1:12 PM | Christie Mason
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

did anyone experience problems when the select statement includes LIKE in the WHERE clause?
10/19/2007 12:41 PM | Sigal Kellermann
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

Thats a master piece. The report which took 4+ mins is getting executed in 1.5 mins.
Thanks a lot.
10/24/2007 5:31 PM | Prabhu Karthic
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

In response to BrianQ's problem regarding wrong sorting with months: "x,y,1,10,11,12,2,3,4,5,6,7,8,9 instead of x,y,1,2,3,4,5,6,7,8,9,10,11,12.", I fixed it by padding '0's to the month name, something like:


SELECT RIGHT(''0''+CAST(DATEPART(mm,<MYDATEHERE>) as nvarchar),2) AS [Month]..... in the sql statement

Replace <MYDATEHERE> with your own date field.
7/2/2008 12:41 PM | Per
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

Hello, the stored proc is very useful for me. but i have one question,
is there a way to create a new line under this statement:

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

the "Hours" Column name is in the new line.

Col1 Col2 Column3
Hours MaxDate Hours

....
thanks in advance
8/28/2008 3:25 AM | Mac Milan
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

I tried to run the SP but on execution I get a error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
My This was the parameters entered in SQL 2000

DECLARE @RC int
DECLARE @Select varchar(1000)
DECLARE @PivotCol varchar(100)
DECLARE @Summaries varchar(100)
DECLARE @GroupBy varchar(100)
DECLARE @OtherCols varchar(100)
SELECT @Select = 'SELECT * FROM vw_Results_IA_OU'
SELECT @PivotCol = '[Date]'
SELECT @Summaries = 'Sum([Fuels Result])'
SELECT @GroupBy = 'OU'
EXEC @RC = [Global_Dashboard].[dbo].[CrossTab] @Select, @PivotCol, @Summaries, @GroupBy, DEFAULT
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Global_Dashboard.dbo.CrossTab'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
9/12/2008 5:47 AM | Emil
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

Hey Jeff.

Firstly (grease grease) this is an incredible function, getting around the issue for ages, and stumbling across this makes me wonder how I got on without it ;).

I have an issue that I am trying to get around at the moment though and I am not sure how to progress it...

I have a query I am getting that retrieves a set of results in the orger I need (I have to do a silly thing to get the ordering working correctly).

When I plug this into the crosstab function its losing that ordering (or at least not operating as I need it to).

This is the query I am basing the crosstab on...

SELECT top 100 percent * FROM wqm_sample_information_vapp where method_id like '%BOD%' and zone_code like '%Mana%' and collected_datime between dateadd(year,-1,getdate()) and getdate() Order by convert(int,Substring(zone_code,6,len(zone_code)))

the silly ordering I have to do is because zone_code has data as :
mana_1
mana_2
...
mana_10
mana_11

and I need it ordered as such rather than below
mana_1
mana_10
...
mana_2

I am then using the crosstab function to then translate this resultset into columns as follows:

exec crosstab 'SELECT top 100 percent * FROM wqm_sample_information_vapp where method_id like ''%BOD%'' and zone_code like ''%Mana%''and collected_datime between dateadd(year,-1,getdate()) and getdate() Order by convert(int,Substring(zone_code,6,len(zone_code)))','zone_code','max(value else '''')[]','collected_datime','null'

but I am finding that the order left to right is different than the order of the data in the select query...

the output of the crosstab looks like this

collected_datime Mana_1 Mana_10 Mana_12 Mana_13 Mana_5 Mana_7 Mana_8 Mana_9
------------------------------------------------------ ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
2007-10-08 11:25:00.000 NULL <1
2007-10-08 14:00:00.000 NULL <1

and I need it like this

collected_datime Mana_1 Mana_5 Mana_7 Mana_8 Mana_9 Mana_10 Mana_12 Mana_13
------------------------------------------------------ ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
2007-10-08 11:25:00.000 NULL <1
2007-10-08 14:00:00.000 NULL <1


Is there enough information in this post to work with or do I need to try and explain more...

Can you please assist, I am struggling.

Oh I have also tried changing the pivot from "zone_code" (of the format "mana_10") to "convert(int,Substring(zone_code,6,len(zone_code)))" and treating it as an integer but the order is still the same and incorrect.

Thoughts very much appreciated...

I am using sql server 2000.

Cheers

Chris
9/25/2008 11:04 PM | Chris Veale
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

hi
i am using sql 2000 i have a problem,
i have this data on a text file:

name mohamed
birth 1980
job engineer

name hassan
birth 1978
job doctor

name .......
.....
.....
.....

i want to import it via DTS to an sql table &quot;with a format&quot; like this:
name birth job
mohamed 1980 engineer
hassan 1978 doctor
..........
..........
..........

please help me,
what can i do ???
i have a hudge problem at my work,
Thanks a lot
Eng. Moahmed Hazem
11/10/2008 3:32 AM | mohamed hazem
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

aa
11/24/2008 7:42 AM | mosi
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

After a bit of debug I found my issue. Hope this helps someone else...
Format of summary per one of the example above:
---> SUM(qty ELSE 0)[Qty]
7/23/2009 3:37 PM | Dave
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

You also need to escape out your quotes within the @select param
example
--->event_start between ''6/1/2009'' and ''6/30/2009''
7/23/2009 3:41 PM | Dave
Gravatar

# re: Another Dynamic SQL CrossTab Stored Procedure

Great topic... to thke it one step further how do I insert the results into a table?
7/19/2010 2:32 PM | Mark
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET