"dynamische" Spalten

Question

Hallo miteinander,

ich hab folgendes Szernario:

Ich hab eine Tabelle Fahrzeug:

Fahrzeug_id int

Fahrzeug_Fahrgestellnummer nvarchar

 

dann noch eine Tabelle Kosten

Kosten_id int

Kosten_Fahrzeug_id int

Kosten_Kostenart_id int

Kosten_Betrag decimal

 

dann och eine Tabelle Kostenart:

Kostenart_id int

Kostenart_bez nvarchar

 

der Inhalt sieht so aus:

Kostenart:

1    Kundendienst

2    Zahnriemen

(können beliebig viele sein)

 

Fahrzeug:

1     Fgstnr1   

2     Fgstnr2

3     Fgstnr3

 

Kosten:

1      3      1       10,00

2      3      2       30,00

3      3      1       20,00

4      1      2       10,00

5      2      2       20,00

6      1      1       15,00

 

Das sind jetzt nur exemplarische Daten und können n-viele Datensätze enthalten sein.

Jetzt möchte ich eine abfrage machen bei der das Ergebnis so aussieht:

Fahrzeug_Fahrgestellnummer       Kundendienst      Zahnriemen

Fgstnr1                                       15,00                  10,00

Fgstnr2                                       0,00                    30,00

Fgstnr3                                      30,00                   30,00

 

Die Anahl der Spalten wäre dynamisch, je nach was in Kostenart drin steht, und das ganze müsste mit ca. 1000 FahrzeugDatensätzen und dazu jeweils ca. 10 Kosten auch einigermassen performant sein.

Mein Ansatz, was ich bau anhand der Einträge eine neue Tabelle und befülle diese und frage diese danach ab, was ja gehen würde.

Aber ich will auch was neues lernen ;-), darum würde ich mich über Anregungen freuen (da ich da auch nicht so fit bin)

 

Vielen Dank und ein schönes Wochenende

Reiner

 

 

 

 

Answer 1

hi,

Dynamisches Pivot sind die Schlüsselwörter, z.B.:

http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-1.html

http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-2.html

http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-3.html

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Hallo Reiner,

das ist ein immer wieder gefragtes Problem. Leider ist die PIVOT-Funktionalität nicht immer das Wahre, da auch in diesem Fall davon ausgegangen wird, dass man schon im Vorfeld weiß, welche Attribute verwendet werden müssen. Ich habe Dir mal das von Dir gepostete Beispiel gescripted. Es mag gerade im letzten Cursor etwas "kompliziert" erscheinen, ist es aber nicht. Letztendlich "baue" ich durch dieses Statement lediglich ein INSERT oder UPDATE-Statement, dass dann für jeden Eintrag ausgeführt wird.

USE tempdb
GO

SET NOCOUNT ON

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('dbo.Kosten', 'U'))
  DROP TABLE dbo.Kosten
  GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('dbo.KFZ', 'U'))
  DROP TABLE dbo.KFZ
  GO

CREATE TABLE dbo.KFZ
(
  Fahrzeug_Id	int	NOT NULL	IDENTITY (1, 1),
  Fahrzeug_FahrgestellNummer	nvarchar(20),
	
  CONSTRAINT pk_KFZ PRIMARY KEY NONCLUSTERED (Fahrzeug_Id),
  CONSTRAINT ix_KFZ UNIQUE CLUSTERED (Fahrzeug_FahrgestellNummer)
)
GO

INSERT INTO dbo.KFZ
(Fahrzeug_FahrgestellNummer)
SELECT	'FG_NR_1'
UNION
SELECT	'FG_NR_2'
UNION
SELECT	'FG_NR_3'
GO

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('dbo.KostenArt', 'U'))
  DROP TABLE dbo.KostenArt
  GO

CREATE TABLE dbo.KostenArt
(
  KostenArt_Id	int	NOT NULL	IDENTITY (1, 1),
  KostenArt_Bez	nvarchar(20),
	
  CONSTRAINT pk_KostenArt PRIMARY KEY NONCLUSTERED (KostenArt_Id),
  CONSTRAINT ix_KostenArt UNIQUE CLUSTERED (KostenArt_Bez)
)
GO

INSERT INTO dbo.KostenArt
(KostenArt_Bez)
SELECT	'Zahnriemen'
UNION
SELECT	'Kundendienst'
UNION
SELECT	'Sonstnochwas'
GO

CREATE TABLE dbo.Kosten
(
  Kosten_Id			int	NOT NULL IDENTITY (1, 1),
  Kosten_Fahrzeug_id	int	NOT NULL,
  Kosten_Kostenart_id	int	NOT NULL,
  Kosten_Betrag		smallmoney
	
  CONSTRAINT pk_Kosten PRIMARY KEY NONCLUSTERED (Kosten_Id),
  CONSTRAINT ix_Kosten UNIQUE CLUSTERED
  (
   Kosten_Fahrzeug_id,
   Kosten_Kostenart_id
  ),
	
  CONSTRAINT fk_KFZ FOREIGN KEY (Kosten_Fahrzeug_Id) 
  REFERENCES dbo.KFZ (Fahrzeug_Id)
  ON DELETE CASCADE,
	
  CONSTRAINT fk_KostenArt FOREIGN KEY (Kosten_Kostenart_id)
  REFERENCES dbo.KostenArt (KostenArt_Id)
  ON DELETE CASCADE
)
GO

-- Eintragen einer wilden Kombination
DECLARE	@k	int
DECLARE	@a	int

DECLARE c CURSOR
FOR
	SELECT	k.Fahrzeug_Id,
		a.KostenArt_Id
	FROM	dbo.KFZ k, dbo.KostenArt a

OPEN c

FETCH NEXT FROM c INTO @k, @a
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	INSERT INTO dbo.Kosten
	(
		Kosten_Fahrzeug_Id,
		Kosten_KostenArt_id,
		Kosten_Betrag
	)
	VALUES
	(@k, @a, RAND() * 1000)
	
	FETCH NEXT FROM c INTO @k, @a
END

CLOSE c
DEALLOCATE c

-- OK Basis ist geschaffen, jetzt geht es in die eigentliche Programmierung
-- da mit dynamischem SQL gearbeitet wird, muss eine globale temp-Table
-- verwendet werden
IF EXISTS (SELECT * FROM tempdb.sys.sysobjects WHERE id = OBJECT_ID('##ResultTable'))
  DROP TABLE tempdb..##ResultTable
  GO

DECLARE	@stmt	nvarchar(1500)
SET	@stmt	=	'CREATE TABLE ##ResultTable (Fahrzeug_FahrgestellNummer nvarchar(20), '

SELECT	@stmt	=	@stmt + r.ColumnDef
FROM
(
  SELECT DISTINCT
     a.KostenArt_Bez + ' smallmoney, '	AS ColumnDef
  FROM	 dbo.Kosten k INNER JOIN dbo.KostenArt a
		ON (k.Kosten_Kostenart_id = a.KostenArt_Id)
) r

-- Letztes Komma durch eine ) ersetzen
SET	@stmt	=	LEFT(@stmt, LEN(@stmt) - 1)
SET	@stmt	=	@stmt + ')'

-- Tabelle erstellen
EXEC sp_executeSQL @stmt

-- Erstellen der SQL-Statements
DECLARE	c CURSOR
FOR
	SELECT	'IF EXISTS (SELECT TOP 1 * FROM ##ResultTable WHERE [Fahrzeug_FahrgestellNummer] = ''' + k.Fahrzeug_FahrgestellNummer + ''')
		UPDATE ##ResultTable SET [' + ka.KostenArt_Bez + '] = ' + CONVERT(varchar, ko.Kosten_Betrag) + '
	ELSE
		INSERT INTO ##ResultTable
		([Fahrzeug_FahrgestellNummer], [' + ka.KostenArt_Bez + '])
		VALUES
		(''' + k.Fahrzeug_FahrgestellNummer + ''', ' + CONVERT(varchar, ko.Kosten_Betrag) + ')'
	FROM	dbo.KFZ k INNER JOIN dbo.Kosten ko
			ON (k.Fahrzeug_Id = Kosten_Fahrzeug_Id) INNER JOIN dbo.KostenArt ka
			ON (ko.Kosten_Kostenart_id = ka.KostenArt_Id)

OPEN c

FETCH NEXT FROM c INTO @stmt
WHILE @@FETCH_STATUS <> -1
BEGIN
	EXEC sp_executeSQL @stmt
	FETCH NEXT FROM c INTO @stmt
END

CLOSE c
DEALLOCATE c

-- So, was haben wir jetz
SELECT * FROM ##ResultTable
SET NOCOUNT OFF

---

Uwe Ricken

MCIT Database Administrator 2005
MCIT Database Administrator 2008
MCTS SQL Server 2005
MCTS SQL Server 2008, Implementation and Maintenance
db Berater GmbH
http://www-db-berater.de