SQLServerCentral / Discuss Content Posted by Peter Larsson / Article Discussions / Article Discussions by Author / Pivot table for Microsoft SQL Server / Latest Posts

RE: Pivot table for Microsoft SQL Server

SwePeso — Wed, 01 Aug 2012 04:57:55 GMT

You're welcome.

RE: Pivot table for Microsoft SQL Server

chris osborn — Wed, 01 Aug 2012 04:48:11 GMT

Thanks! I have been looking for a way to replicate pivots on our old SQL environments and this works quite nicely =)

RE: Pivot table for Microsoft SQL Server

nmonroe — Fri, 13 Jan 2012 00:32:57 GMT

A few years too late but thank you for this!This is a fantastic work-around and your method of explanation is very easy to understand!

RE: Pivot table for Microsoft SQL Server

mdevenney — Fri, 28 May 2010 13:43:38 GMT

Awesome! Thanks! I'm trying to write a report in SSRS and this was exactly what I needed to clean up my failed attempts at using PIVOT. Multiple columns in a PIVOT FTW!

RE: Pivot table for Microsoft SQL Server

Dave Ballantyne — Wed, 30 Dec 2009 03:22:12 GMT

[quote][b]roopesh.purohit (12/30/2009)[/b][hr]Thanks Peter - nice article.I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format for Example :-I have :-Survey Type , SurveyName , SurveyCode , QuestionText , Answer/Response , comments these fileds now I need to convert it into :-Survey Type , SurveyName , SurveyCode , Question 1 , Answer1 , Question 2 , Answer 2 ------Question 6, Answer 6 , Coments , Average By User. Can you please tell me how can I do this by using your code you mentioned in the Pivot Table Article?[/quote]If you are using SSRS then column groupings would probably be a better solution for you...

RE: Pivot table for Microsoft SQL Server

roopesh.purohit — Wed, 30 Dec 2009 03:06:41 GMT

Thanks Peter - nice article.I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format for Example :-I have :-Survey Type , SurveyName , SurveyCode , QuestionText , Answer/Response , comments these fileds now I need to convert it into :-Survey Type , SurveyName , SurveyCode , Question 1 , Answer1 , Question 2 , Answer 2 ------Question 6, Answer 6 , Coments , Average By User. Can you please tell me how can I do this by using your code you mentioned in the Pivot Table Article?

RE: Pivot table for Microsoft SQL Server

cbrooks-501370 — Sun, 22 Feb 2009 00:14:54 GMT

I too am having the same problem. Creating a sum of a time spent numeric field from a support ticket database. The data shows correctly if you view the #aggregates table. When you run the query for #rows, it removes the decimal point and everything after. ( 214.50 becomes 214). When declaring the datatype as MONEY, the final resultset rounds the value. It looks like there is something happening in the exec (@sql) command.Great code though...will save a ton of time!UPDATE: This line: SELECT @SQL = 'ALTER TABLE #ROWS ADD ' + QUOTENAME(@COLUMNTEXT) + ' [b]INT [/b]DEFAULT 0'is what changes the datatype for the CellData. I've corrected by changing it to:SELECT @SQL = 'ALTER TABLE #ROWS ADD ' + QUOTENAME(@COLUMNTEXT) + ' [b]NUMERIC(19,2)[/b] DEFAULT 0'

RE: Pivot table for Microsoft SQL Server

Krasavita — Mon, 10 Nov 2008 13:22:14 GMT

Thank you, but I don't know how to do it, can you please help me I would appreciate

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 10 Nov 2008 13:13:10 GMT

Well... sp_crosstab is not my code so you will have to ask that author.

RE: Pivot table for Microsoft SQL Server

Krasavita — Mon, 10 Nov 2008 13:05:57 GMT

I am a little confused,This is what I have:EXECUTE sp_crosstab 'SELECT shipdate FROM tblvwUPS_package_Detail_Daily GROUP BY shipdate', 'sum (packages)','service','tblvwUPS_package_Detail_Daily 'Where do I put :SELECT *INTO tblvwUPS_package_Detail_DailyPackagesFROM #Rows ? Thank you

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 10 Nov 2008 12:52:17 GMT

No. You can also have SELECT *INTO Table1FROM #Rowsat the end of the stored procedure.

RE: Pivot table for Microsoft SQL Server

Krasavita — Mon, 10 Nov 2008 12:45:02 GMT

So, I have to create Table1 with the fields(values0 I will have?

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 10 Nov 2008 11:51:56 GMT

INSERT Table1EXEC dbo.CrossTabProcedure ...

RE: Pivot table for Microsoft SQL Server

Krasavita — Mon, 10 Nov 2008 11:47:57 GMT

I used your cross tab procedure and it work's beatifully,but how can I put results into table? Thank you

RE: Pivot table for Microsoft SQL Server

SwePeso — Fri, 07 Mar 2008 10:04:29 GMT

Thank you!It's nice to see someone recognize the simpicity of the algorithm structure.You are absolutely correct about the quick start, to only change the insert thingy with SELECT .. GROUP BY portion.I also tried to make it easy for future use to add more columns with different kind of aggregations. Just add another portion of dynamic sql and execution.

RE: Pivot table for Microsoft SQL Server

Charles Wannall — Thu, 06 Mar 2008 09:34:39 GMT

Outstanding post! I have been annoyed for years that what we did in MS Access with no effort whatsoever took so much fiddling in SQL Server. Talk about logical absurdity!I didn't know enough to re-do using the column index info and create this beautiful solution.For those who want to use this: If you just want to copy and paste the thing and use it quickly, you only have to alter the SELECT...FROM...GROUP BY... portion of the posted code and you're running.Now THAT is good post, given the nature of this topic especially.Thanks an enormous bunch.

RE: Pivot table for Microsoft SQL Server

larry-610474 — Thu, 31 Jan 2008 17:36:00 GMT

Peter,Your code is so well-written and useful. Thank you!I would like to add a column at the end for totals and a row at the bottom for totals. Can you steer me in the right direction?

RE: Pivot table for Microsoft SQL Server

jones.justinw — Tue, 06 Nov 2007 14:23:59 GMT

This is a great article. Is it possible to use this to create a view? Thanks!

RE: Pivot table for Microsoft SQL Server

SwePeso — Wed, 03 Oct 2007 00:23:10 GMT

[quote][b]Jimbo Bantog (5/9/2007)[/b][hr]I need this column to be numeric (9,2). I tried this but it's showing 1234 instead of 1234.15. What am I doing wrong here? Please help.[/quote]It's hard to tell without knowing which modifications you have made.

RE: Pivot table for Microsoft SQL Server

Wayne West — Tue, 02 Oct 2007 15:48:47 GMT

Man, I love that code! I just put it to work and it did the job perfectly!

RE: Pivot table for Microsoft SQL Server

Jimbo Bantog — Wed, 09 May 2007 19:56:00 GMT

Hi Peter,

Thanks for an excellent article on pivot tables. I'm not sure if this has been asked before (there is a problem when I clicked on the Next Page link). In the example that you gave, the CellData column is MONEY type, I need this column to be numeric (9,2). I tried this but it's showing 1234 instead of 1234.15. What am I doing wrong here? Please help.

Thanks,

Jim

RE: Pivot table for Microsoft SQL Server

rukmang rege — Fri, 06 Apr 2007 05:01:00 GMT

I was just going through pivot example.As you said in pivot you need to hard wire columns. Can we not built a string of column values dynamically for dynamic pivot generation.Please let me know.Ref article:http://www.sqlteam.com/item.asp?ItemID=2368

RE: Pivot table for Microsoft SQL Server

rukmang rege — Thu, 05 Apr 2007 00:52:00 GMT

Thanks for the post.

Very simple,perfect.Would like to see similar post from you

RE: Pivot table for Microsoft SQL Server

SwePeso — Tue, 03 Apr 2007 22:51:00 GMT

1) Why would I? Then you have no control over the datatype for the aggregated column VALUE (sum).2) The syntax is wrong. It should be SELECT .. INTO...FROM ... WHERE ...

RE: Pivot table for Microsoft SQL Server

Marc Brooks — Tue, 03 Apr 2007 17:39:00 GMT

You could update the table creation to get the datatype of temporary table directly from the source instead of the hard-coded types/sizes. This would be easily done with:

SELECT RowText, ColumnText, Value FROM Source WHERE 1=0 INTO #Aggregates

Which works because the 1=0 clause insures no rows are matched, but the datatypes are set according to the soruce...

RE: Pivot table for Microsoft SQL Server

SwePeso — Fri, 30 Mar 2007 03:00:00 GMT

The article was re-released today, so don't take it personal

About PIVOT and UNPIVOT in SQL Server 2005; I have more use for UNPIVOT than PIVOT...

RE: Pivot table for Microsoft SQL Server

Sharon Matyk — Fri, 30 Mar 2007 02:09:00 GMT

Hi - based on the dates of the other comments, I'm coming to the party a little late. But thanks for a very clearly-explained (and with two examples) piece of code. I too have wondered - I've been on SQL since v6.x - how to do a crosstab when the column number is variable. I'll be interested to see PIVOT and UNPIVOT on 2005.

RE: Pivot table for Microsoft SQL Server

Simeon Herbert — Tue, 27 Mar 2007 13:07:00 GMT

Fantastic Article,

I now need to somehow work out to add ordering based on these "unknown" columns but this is a great start to stuff I really did not understand.

A Huge help - thanks.

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 17 Jul 2006 14:14:00 GMT

You are welcome!

I am satisfied you liked the article. I really struggled to keep it simple and consise. And as you write, my purpose was to give away the base of what pivot tables really are.

RE: Pivot table for Microsoft SQL Server

silvio costa — Wed, 21 Jun 2006 07:29:00 GMT

You got my Excellent! I've seen quite a few procedures, but I really appreciate your methodology: you paved a complete road from the ground up with concise and adequate explanations. I shall use it as a solid base for my pivot table reports. Thanks a lot.

Silvio E. Costa

RE: Pivot table for Microsoft SQL Server

R. M. Joseph — Tue, 13 Jun 2006 02:44:00 GMT

Thanks Peter. It is very good procedure and I shall use it.

R. M. Joseph

RE: Pivot table for Microsoft SQL Server

SwePeso — Tue, 13 Jun 2006 00:18:00 GMT

You're welcome!

Now I have to look up "arduous" in my dictionary...

RE: Pivot table for Microsoft SQL Server

SwePeso — Tue, 13 Jun 2006 00:17:00 GMT

Max number of columns are restricted to what datatype you use for #Aggregate column. The total size for a row is 8,060 bytes. Using 60 bytes for RowText leaves us 8,000 bytes for the rest of the columns and since INT is 4 bytes, we can potentially have 2,000 columns. If you prefer SMALLINT (2 bytes) as #Aggregate column, you could theoretically have 4,000 columns.

RE: Pivot table for Microsoft SQL Server

John Rempel — Mon, 12 Jun 2006 15:40:00 GMT

Perfect timing! I was able to use this logic for a report today. It made short work out of an otherwise arduous task.Thanks.

RE: Pivot table for Microsoft SQL Server

teleMarinaV — Mon, 12 Jun 2006 08:40:00 GMT

I think it is worth mentioning the restrictions of that method such as:

- MAX number of columns (?),

- MAX total row length (8K).

I've used similar method using cursor instead of column table.

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 12 Jun 2006 08:01:00 GMT

Yes, SQL injection could possible be an issue here, if the data in ColumnText is written such way.

But since the beginning of the UPDATE-statement is hardwired with "UPDATE", I right now can't see a way to manipulate the statement to run SQL injection code.

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 12 Jun 2006 07:57:00 GMT

Thanks Ryan.

I have tried to keep the dynamic SQL executions to a minimum for obvious speed reasons. What I think I have provided is a base for creating crosstabs/pivots for users to learn from and hopefully, evolve with.

There are some things to do with the code to make it "universal". But I thought it would be best to show how to start from the beginning.

To make the code "universal" you first have to build a dynamic query for preaggregating the data in #Aggregates. That is not very hard to to! Also, you must change the parameters to the future stored procedure to allow two fully qualified names such as RemoteServer1.OwnerLocal.ThatTable.ThisField for the rows and columns. Even the CellData field in #Aggregates could be taken from a parameter this way and called with 'SUM(x)' or 'COUNT(y)'. You catch my drift.

An example could be

EXEC dbo.CrossTabPivot 'Table1.OfficeName', 'Table2.Category', 'COUNT(t)', ...

RE: Pivot table for Microsoft SQL Server

SwePeso — Mon, 12 Jun 2006 07:55:00 GMT

Yes, in my very first early version. Then I realized that I could potentially come across the 8000 character limit for varchars.

RE: Pivot table for Microsoft SQL Server

RyanRandall — Mon, 12 Jun 2006 06:17:00 GMT

Thanks Peter - nice looking article.I've not read the whole article yet (that's for when I have more time), but I wonder how your approach compares with the 'classic' crosstab/pivot table approaches...http://www.sqlteam.com/item.asp?ItemID=2955 http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspxIf anyone has time to evaluate this, I'd be very grateful!

RE: Pivot table for Microsoft SQL Server

Mike Dougherty — Mon, 12 Jun 2006 05:54:00 GMT

Have you considered building the update string inside the loop, but executing it after you have iterated all the columns? O(1) < O(n)Also, do you have any comments/caveats on the security implication of dynamic SQL used in this process?