dynamische Pivot-Tabelle als Prozedur - letztes Problem: StringLänge

[Stefanie Buchholz]

Hallo Zusammen,

ich haber eine Vorlage im Internet gefunden für die Erzeugung einer dymsmichen Pivot-Tabelle (Tabelle, in der die Anzahl Spalten vorher nihct bekannt ist).

Wie man im Code sehen kann habe ich dies noch auf meine Anforderungen etwas angepasst.
Der erste test klappe auch super, dann habe ich den zweiten Test mit der Northwind durchgeführt, indem ich andere Spalten mir zeigen lassen wolle. Scheinbar sprengt da jetzt der String für die CASE-Anweisung alles und irgendetwas von den letzteren Aufrufen klappt nicht richtig.

Ich habe auch noch nicht so viel Ahnung, habe mir das Verständnis für die Abfrage stundenlang zusammengesucht nur diesen Fehler kann ich einfach nicht finden.

Ich hoffe ihr könnt helfen.

Liebe Grüße aus Bremen und Gutes Nächtle
Stefie


[highlight=sql]
/* Basis für den Query kommt von der Website: http://www.itrain.de/knowhow/sql/tsq...sform_v1_1.asp
* Alle zusätzlichen Summenbildungen wurden entfernt
* Getestet auf der Nothwind-DB
*
* Erweiterung des Ursprungsquery um weitere Spalten in der Ausgabetabelle mit Hilfe der Variable @More_Select
* @More_Select wird vorbelegt mit NULL um variabel zu halten, ob weitere Spalten ausgegeben werden sollen
*/

-- Prüfen, ob Prozedur vorhanden ist, wenn ja Löschen
IF OBJECT_ID('sp_Transform', 'P') IS NOT NULL
DROP PROCEDURE sp_Transform
GO
-- Anlegen der Prozedur mit den Parametern
CREATE PROCEDURE sp_TRANSFORM
@Aggregate_Function nvarchar(max),
@Aggregate_Column nvarchar(max),
@TableOrView_Name nvarchar(max),
@Select_Column nvarchar(max),
@Pivot_Column nvarchar(max),
@More_Select nvarchar(max) = null,
@DEBUG bit = 0
AS
SET NOCOUNT ON

-- Festlegen der zusätzlichen Variablen zur Erzeugung des dynamischen SQL-Strings
DECLARE @TransformPart nvarchar(max)
DECLARE @SQLColRetrieval nvarchar(max)
DECLARE @SQLSelectIntro nvarchar(max)
DECLARE @SQLSelectFinal nvarchar(max)
DECLARE @SQLSelectFinalGroup nvarchar(max)

-- Prüfung, ob eine gültige Aggregatfunktion übergeben wurde
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
-- Erzeugen der SELECT-Spalte
SELECT @SQLSelectIntro = 'SELECT '+ (@Select_Column) + ', '
IF @DEBUG = 1 PRINT @sqlselectintro
-- Erzeugen der CASE-Anweisung
SET @SQLColRetrieval =
N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
N'''' + @Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(max))) +
N' AS VARCHAR(max)) WHEN '''''' + CAST(' +
QUOTENAME(@Pivot_Column) +
N' AS NVarchar(max)) + '''''' THEN ' + @Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(max))) +
N') ELSE @TransformPart + '', ' + @Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
N' AS nVARCHAR(max)) WHEN '''''' + CAST(' +
QUOTENAME(CAST(@Pivot_Column As VarChar(max))) +
N' AS nVARCHAR(max)) + '''''' THEN ' +
@Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(max))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@Pivot_Column AS VARCHAR(max))) +
N' FROM ' + @TableOrView_Name + ') SelInner'
-- Recherchieren was machen N + SelInner + @TransformPart + QUOTENAME eigentlich???
IF @DEBUG = 1 PRINT @SQLColRetrieval
-- Länge prüfen
print LEN(@SQLColRetrieval)
-- Ausführen der CASE-Anweisung mit Speicherung in der Variable
EXEC sp_executesql @SQLColRetrieval,
N'@TransformPart nvarchar(max) OUTPUT',
@TransformPart OUTPUT
IF @DEBUG = 1 PRINT @TransformPart
print LEN(@TransformPart)
-- Gruppierung nach der SELECT-Spalte
SET @SQLSelectFinal =
N' FROM ' + @TableOrView_Name + N' GROUP BY ' +
@Select_Column
IF @DEBUG = 1 PRINT @SQLSelectFinal
-- Wenn ein weiterer Parameter für weitere Spaten übergeben wurden, Anweisung erweitern
IF @More_Select is not null SET @More_Select = N' , ' + @More_Select
IF @DEBUG = 1 PRINT @More_Select
-- Zusätzliche Ausgabe des gesamten SQL-Strings zum prüfen
print (@SQLSelectIntro + @TransformPart + @More_Select + @SQLSelectFinal + @More_Select)
print LEN(@SQLSelectIntro + @TransformPart + @More_Select + @SQLSelectFinal + @More_Select)
-- SQL Ausführen

EXEC (@SQLSelectIntro + @TransformPart + @More_Select + @SQLSelectFinal + @More_Select)
END
GO


-- Aufruf der Prozedur mit der Nothwind-DB - ok
USE NORTHWIND
GO
EXEC sp_transform 'sum', 'UnitPrice', 'dbo.Products', 'ProductName', 'SupplierID' --, 'UnitsInStock,QuantityPerUnit,ReorderLevel' --zusätzliche Spalten
GO

-- Fehlerhaft (siehe erste Test mit der Print-Ausgabe)
USE NORTHWIND
GO
EXEC sp_transform 'sum', 'UnitPrice', 'dbo.Products', 'SupplierID', 'ProductName' --, 'UnitsInStock,QuantityPerUnit,ReorderLevel'
GO
[/highlight]