Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 11 posts - 121 through 130 (of 130 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 1

Ah... I feel much better now. I had seen the BOL info before but thought someone had come up with a new bit of info where they weren't replacing the deprecated forms of WITH ROLLUP and CUBE. I was really concerned because I use the two clauses on a regular basis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eq2home SSC Enthusiast Points: 170 More actions · Answer 2

I reported the depreciation of the WITH CUBE functionality.

This is an interesting case that requires more investigation. Microsoft's Excel 2007 does not respect the NULL rows for subtotaling in the returned cursor using Microsoft Query into an Excel Pivot table. (By the way, kudos to Microsoft for the GREAT improvement in Excel 2007's handling of Pivot integration with SQL Server...more on this at a later time.)

When Excel grabs the Cube and puts it into Excel (again great job on the automation but Stored Procedures are not straight forward) it doubles all counts because it turns null into a row in the cube and calls it (blank).

Ok, so the solution is busy work (turn off the (blank) fields in all row groups. It is a bit clumsy but it works. The bigger question is shouldn't Microsoft take advantage of 2008 functionality and automatically adjust for the NULL (blank) rows coming from SQL Server 2008 in Excel?

This was the question I posed to the Microsoft Excel 2007 Team. The response from the Microsoft support team was "don't use this feature because it was being depreciated." Ok, fine.

Notice that the answer was not use the CUBE() feature. The reason it was not use the CUBE() feature was that the Excel Team had not caught up with the SQL Team.

So, with the CUBE() or WITH CUBE (much appreciation to the submission that cleared up the confusion on support for the 2008 TSL CUBE) functionality we are back at the same question "when will Microsoft fully support (automate) the CUBE functionality in Excel 2007 or higher?

Again, my hat is off to Microsoft's efforts to integrate Excel and SQL Server but I want (and my customers surely do) better automation support between Excel and SQL Server. The last time I checked both products were made by the same company and that company.

Cheers and Jeff again THANKS for your enlightening original article. I WoWed some customers this weekend based on some of the fundamentals your article provided.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 3

Outstanding. Always makes my day when someone can apply something they learned from one of my articles. Thanks for the great feedback.

On the other stuff... I'm one of those that wish MS would get things together a bit better across their products. I know I'll get some arguments on this but using Excel as a reporting tool for SQL Server is something that a whole lot of people correctly need to do. You'd think that MS would make it a little easier to do even via T-SQL sans SSRS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eq2home SSC Enthusiast Points: 170 More actions · Answer 4

I have been discussing the WITH CUBE, CUBE() and Excel Pivot Table functionality with Microsoft's SQL Server and Excel Teams. Here is the highlights of the discussion to date:

WITH CUBE is deprecated but works with 80, 90 and 100

CUBE() will be supported moving forward but is only works with 100

The discussion is continuing on whether Excel Pivot Tables will fully support a CUBE (NULL subtotal rows) properly when the CUBE is returned from a TSQL CUBE() function.

vadnal.vijaykumar Valued Member Points: 62 More actions · Answer 5

Hi Jeff

Thanks for the informative article.

But with this same posting, I am currently working on some Reports using ASP.NET and SQL Server. I want to populate data from 3 tables. Out of which 1 is used for populating Table schema for report layout which could contains a transactional data for a particular year. Then second could contains Number's which is being used as first column in the report and with this Number reference I am gonna call all the Codes Amount and Count from Code table i.e. my last table.

For schema generation I have written Cursor, so that it will populate transaction data and dynamically added those rows in Create/Alter table script.

For next I have written simple SP to return data table's 1 is for Number and another for Codes.

Then I am looping through 1 table(Number) then in nested loop for Codes I have passed both Number and Code to get my Amount and Count from ASP.NET coding.

All is fine but, I want to do this from the SQL Server directly not by using ASP.NET, because I have measured performance it is populating data with least performance.

Thanks,

Vijay

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 6

vadnal.vijaykumar (7/26/2010)
Hi Jeff
Thanks for the informative article.
But with this same posting, I am currently working on some Reports using ASP.NET and SQL Server. I want to populate data from 3 tables. Out of which 1 is used for populating Table schema for report layout which could contains a transactional data for a particular year. Then second could contains Number's which is being used as first column in the report and with this Number reference I am gonna call all the Codes Amount and Count from Code table i.e. my last table.
For schema generation I have written Cursor, so that it will populate transaction data and dynamically added those rows in Create/Alter table script.
For next I have written simple SP to return data table's 1 is for Number and another for Codes.
Then I am looping through 1 table(Number) then in nested loop for Codes I have passed both Number and Code to get my Amount and Count from ASP.NET coding.
All is fine but, I want to do this from the SQL Server directly not by using ASP.NET, because I have measured performance it is populating data with least performance.
Thanks,
Vijay

Hi Vijay,

I have two recommendations here... my first would be that only those folks who have ever clicked on the article will ever see your posting above. Since I don't always have the time to post an answer, my recommendation would be for you to post your question on the appropriate (2000, 2005, or 2008) forum. Before you do that, my second recommendation would be to read the article at the first link in my signature line below so you stand the chance of getting really good answers.

I would also keep in mind that people are going to ask you what you've done to try to resolve your own problem in the form of posted code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Sterbal SSChampion Points: 11050 More actions · Answer 7

Is there a simple way to get a single row table with variable numbers of columns to output with 2 columns?

i.e.

colA colB colC

1 5 23

colA 1

colB 2

colC 3

and handle an extra column easily:

colA colB colC colD

1 5 23 743

colA 1

colB 2

colC 3

colD 743

412-977-3526 call/text

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

robert.sterbal 56890 (7/7/2015)
Is there a simple way to get a single row table with variable numbers of columns to output with 2 columns?
i.e.
colA colB colC
1 5 23
colA 1
colB 2
colC 3
and handle an extra column easily:
colA colB colC colD
1 5 23 743
colA 1
colB 2
colC 3
colD 743

"Simple" is a relative term. Which version of SQL Server are you using? A relatively simple version would entail reading syscolumns for the table and then dynamic SQL to construct a query from the results.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Robert Sterbal SSChampion Points: 11050 More actions · Answer 9

ChrisM@Work, thanks for your reply.

Simple means that someone else has a solution that I can tweak for my own purposes.

I was going down too many rabbit holes with search.

412-977-3526 call/text

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 10

robert.sterbal 56890 (7/7/2015)
Is there a simple way to get a single row table with variable numbers of columns to output with 2 columns?
i.e.
colA colB colC
1 5 23
colA 1
colB 2
colC 3
and handle an extra column easily:
colA colB colC colD
1 5 23 743
colA 1
colB 2
colC 3
colD 743

You will need to create a dynamic pivot. It will look something like the psuedocode below.

If you provide actual DML/DATA I can provide a working script.

(See this article on how to provide sample data: http://www.sqlservercentral.com/articles/Best+Practices/61537/)

DECLARE @Columns NVARCHAR(MAX)

DECLARE @Query NVARCHAR(MAX)

SELECT @Columns = -- query to get your column names

SET @Query = '

SELECT *

FROM SomeTable

PIVOT

(

SUM(Value)

FOR ColumnName IN

('

+ @Columns +

')

) AS Pivot'

EXEC SP_EXECUTESQL @Query

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 11

PLEASE READ ME!

This article is an old one and I've been remiss in not updating it but you need to be aware of something... The creation of the dynamic SELECT list will usually (which is not good enough) work ok as posted but, over time, it's been demonstrated many times that it can go awry and return anything from NULL, to nothing, to just a partial result. I need to update the code to use the much safer "FOR XML PATH()" method for folks that have SQL Server 2016 or less and the STRING_AGG() method for those that have 2017 or better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)