October 23, 2008 at 6:58 am
Hi
Im trying to create a pivot table, with little success, atm im using this code..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TRANSFORM]
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@Aggregate_Column
name of column for aggregate
@TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@Select_Column
Column for first column in result table
for this column row values are displayed
@Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
@Select_Column='Product', @Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
-------+----------+----------+---------+---------
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
-------+----------+----------+---------+---------
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @TransformPart nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro nvarchar(4000)
DECLARE @SQLSelectFinal nvarchar(4000)
IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@Select_Column) +
', '
IF @DEBUG = 1 PRINT @sqlselectintro
SET @SQLColRetrieval =
N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
N'''' + @Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(@Pivot_Column) +
N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
N') ELSE @TransformPart + '', ' + @Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(CAST(@Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + '''''' THEN ' +
@Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @TableOrView_Name + ') SelInner'
IF @DEBUG = 1 PRINT @SQLColRetrieval
EXEC sp_executesql @SQLColRetrieval,
N'@TransformPart nvarchar(4000) OUTPUT',
@TransformPart OUTPUT
IF @DEBUG = 1 PRINT @TransformPart
SET @SQLSelectFinal =
N', ' + @Aggregate_Function + N'(' +
CAST(@Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
@Select_Column + N' WITH CUBE'
IF @DEBUG = 1 PRINT @SQLSelectFinal
EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
Now ofc i did not write this myself from scratch, but trying to modify it my needs. which are that i have more 'select colums' than just the one here, i have 4 to be exact, and ive tried a few things to alter this to get this to work, with little success... any help/tips/advice etc?
Thx
Oh, and as a note, i know this can be done easier in sql 2005 with the pivot function, but unfornatley, the server this will be running on does not have 2005, but 2000
October 28, 2008 at 7:37 pm
I see you haven't received any answers to your question.
That is because you haven't provided us with enough information to assist you.
Please read the following article.
Forum Etiquette: How to post data/code on a forum to get the best help
🙂
October 29, 2008 at 4:07 am
You seem to have a case of PMS, pivot madness syndrome. You should check out the RAC utility where the madness (code) is eliminated and you can easily solve your problem. If your not a "night of the living dead" type developer/programmer visit:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply