Create Dynamic Cross Tab Queries With spPivot
 |
(Ben Taylor) The concept of a Pivot Table to produce Cross Tabulation Queries has been around for decades. The Pivot Table method of writing a Cross Tab query is the fastest method in SQL Server to date. But Pivot Tables are frequently still not used because they require work to maintain the Pivot Table itself accommodating changes in the data. In this article I provide you with a stored procedure that can dynamically return pivoted data. Additionally, this procedure can persist the Pivot Table created as well as a view so that the code you would have written manually in the past is already done. This tool provides you with both random pivot generation as seen in Microsoft Access along with permanent SQL Server objects working as a code generator.
This article provides an example of a Cross Tabulation Query, how to produce a Cross Tabulation Query with a Pivot Table and instructions on how to utilize the pivot table utility spPivot.
|
|
| | Related Articles - For Members.
|
|
Key (Please note):
(R) - registration may be required for access at the target site
($) - target site may require paid membership for access to this or other content
Reader Comments: Post Your Comments/Feedback
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/3/2005 7:12:10 AM)
Would be a pretty good article if the GET SOURCE CODE link worked.
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/3/2005 9:22:49 AM)
SOURCE CODE LINK BROKEN:(http://www.sswug.org/attachments/sp_pivot.sql) produces a 404 error.
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/3/2005 4:01:12 PM)
Link has been updated - sorry about that. (SW)
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/4/2005 9:53:21 AM)
Is it my imagination or does the article list nothing at all where the example values for parameter @VALUE_XML should be?
Also there were some apparantly cosmetic flaws in the attachment download (missing line break, spurious indentation) but the author might want to verify the code is as he wrote it.
Super article -- my heartfelt thanks to the author!
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/4/2005 10:57:46 AM)
All ----
Your imagination if fine. There is a rendering problem with the site. The text I put in the HTML was < and >. The tool has converted these values to <> which are therefore valid XML and are not rendered.
Here is the contents of the values parameter:
I'll see if Steve can fix it.
Sorry for the inconvenience...
Ben
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/4/2005 11:00:05 AM)
Now I am getting rendering problems again...let me see if I can figure out how to get it to render here now. Last time I entered plain text instead of HTML code. This time I'll try HTML
<VALUE>
<VALUE COLUMN_NAME="Quantity" AGGREGATE="SUM"/>
<VALUE COLUMN_NAME="Quantity" AGGREGATE="COUNT"/>
</VALUES>
Cheers,
Ben
RE: Create Dynamic Cross Tab Queries With spPivot
(posted: 5/6/2005 1:56:10 PM)
I have had numerous Emails from folks trying to get the source or understand how the parameter @VALUE_XML works. It is unfortunate this does not render on the site. If you have problems with the sp working it is probably this parameter. Please send me an email and I will reply with clear instructions to your email regarding how this works. Your Email address will then be deleted by me.
Cheers,
Ben
btaylor@sswug.org
|
