Es gibt eine Menge Programme, die liefern die Daten im folgenden Format:

Fname;email;Firma;Wert1;Wert2;Wert3;Wert4;Wert5;Wert6
willfried;willfried@wfaerber.de;faerber;5;6;7;8;;
sebastian;sf@wfaerber@de;müller;10;20;30;40;50;60

Die Daten sind wie eine Excel Tabelle aufgebaut. Dieses Format ist sehr Platz sparend. Allerdings in einer relationalen Datenbank sollte dieses Format nicht gespeichert werden. Es entspricht nicht den Regeln der Datenbank Normalisierung.

Jede Zeile wird in den "Fname", die "Email" Adresse, die "Firma" und genau einem Wert zerlegt. Aus einer Eingabezeile werden nach der Verarbeitung bis zu sechs Zeilen.

Die Ausgabe sieht dann so aus:

Fname;email;Firma;Key; Wert
willfried;willfried@wfaerber.de;faerber;Wert1;5
willfried;willfried@wfaerber.de;faerber;Wert2;6
willfried;willfried@wfaerber.de;faerber;Wert3;7
willfried;willfried@wfaerber.de;faerber;Wert4;8
willfried;willfried@wfaerber.de;faerber;Wert5;
willfried;willfried@wfaerber.de;faerber;Wert6;
sebastian;sf@wfaerber@de;müller;Wert1;10
sebastian;sf@wfaerber@de;müller;Wert2;20
sebastian;sf@wfaerber@de;müller;Wert3;30
sebastian;sf@wfaerber@de;müller;Wert4;40
sebastian;sf@wfaerber@de;müller;Wert5;50
sebastian;sf@wfaerber@de;müller;Wert6;60

Der eine oder andere wird sicher denken, für diese Transformation ist Script Kode notwendig. Mit Hilfe SSIS Unpivot Transformation geht es auch ohne eine Zeile Kode. Die Transformation steht im Dataflow zur Verfügung.

Damit die Transformation funktioniert müssen bestimmte Werte eingestellt werden:

• Auswahl der Zeilen für die Transformation
• Festlegen der "Pass Through" Spalten
• Festlegen der "Destination Colum"
• Festlegen der "Pivot Key Value"
• Festlegen des "Pivot key value column name"

Um Missverständnisse zu vermeiden möchte ich folgende Definition machen:

Destination Column
Der Name der Spalte, in der der Wert gespeichert wird

Pivot key value column name
Der Name der Spalte, die eine Referenzierung auf die ursprüngliche Spalte enthält.

Pivot Key Value
Die Spalte, deren Werte gedreht werden sollen. Von horizontal nach vertikal.

Auswahl der Zeilen für die Transformation
Die Spalten, die nicht eingelesen werden sollen werden im Fenster nicht in der ersten Spalte und der Spalte "Pass Through" markiert.

Festlegen der "Pass Through" Spalten
Die Spalten, die in jede Zeile übernommen werden, werden in der Spalte "Pass Through" markiert; die erste Spalte wird nicht markiert.

Festlegen der "Destination Colum"
Der Wert, der in "Destination Column" eingetragen wird, wird als Ausgabespaltennamen verwendet.

Festlegen der "Pivot Key Value"
Für den "Pivot Key Value" wird per default auf dem Namen der Eingabespalte gesetzt. Bitte setzen Sie diesen Wert manuell auf einen eindeutigen Namen.

Festlegen des "Pivot key value column name"
Als letztes wird der "Pivot key value column name" auf einen aussagekräftigen Namen festgelegt. Dieser Name wird für die Spaltenüberschrift verwendet.

Bitte achten Sie darauf, dass die Namen der Spalten "Destination Column", "Pivot Key Value" und "Pivot key value column name" unterschiedlich sind.

Das war es …

Nach der Transformation ...

In der Ausgabedatei befinden sich folgende Werte:

Key;Wert;Fname;email;Firma
Wert1;5;willfried;willfried@wfaerber.de;faerber
Wert2;6;willfried;willfried@wfaerber.de;faerber
Wert3;7;willfried;willfried@wfaerber.de;faerber
Wert4;8;willfried;willfried@wfaerber.de;faerber
Wert5;;willfried;willfried@wfaerber.de;faerber
Wert6;;willfried;willfried@wfaerber.de;faerber
Wert1;10;sebastian;sf@wfaerber@de;müller
Wert2;20;sebastian;sf@wfaerber@de;müller
Wert3;30;sebastian;sf@wfaerber@de;müller
Wert4;40;sebastian;sf@wfaerber@de;müller
Wert5;50;sebastian;sf@wfaerber@de;müller
Wert6;60;sebastian;sf@wfaerber@de;müller

Dabei fällt auf, dass zuerst die Pivot Key Value Spalte und dann die Destination column ausgegeben wird. Wenn sie die Reihenfolge der Spalten in der Ausgabe ändern wollen, dann müssen Sie die Struktur der Ausgabedatei manuell mit der Hand definieren.

Können auch mehrere Spalten zusammengefasst werden?

Wie sieht es aus, wenn Sie nicht nur eine sondern zum Beispiel zwei Werte einer Zeile zuordnen müssen. Denken Sie nur an die Ist- und Planwerte. Für die Beispieldatei bedeutet dies: aus einer Eingabezeile werden nach der Verarbeitung bis zu drei Zeilen.

Als Ausgabedaten erwarten wir

Fname;email;Firma;Wert;W1;W2
willfried;willfried@wfaerber.de;faerber;K1;5;6
willfried;willfried@wfaerber.de;faerber;K2;7;8
willfried;willfried@wfaerber.de;faerberK3;;;
sebastian;sf@wfaerber@de;müller;K1;10;20
sebastian;sf@wfaerber@de;müller;K2;30;40
sebastian;sf@wfaerber@de;müller;K3;50;60

Damit diese Aufgabe gelöst werden kann, müssen folgende Bedingungen erfüllt sein:

• Alle Pivot Spalten müssen den gleichen Datentypen haben, zum Beispiel String
• Alle Mitglieder einer Gruppe von Pivot Spalten die zusammen gehören, bekommen den unterschiedliche Namen in Destination Column.
• Alle Pivot Spalten, die eine Gruppe bilden, bekommen den gleichen Pivot Key Value. Dieser Punkt ist entscheidend, sonst bekommen Sie eine Fehlermeldung und es funktioniert nicht.
  Deshalb empfehle ich Ihnen grundsätzlich den Wert dieser Spalte immer manuell zu setzen.

Die Definition im Editor muss so aussehen:

Falls Sie an dieser Stelle einen Fehler machen wird die Unpivot Transformation so markiert.

Die ausführliche Fehlermeldung bekommen Sie, wenn Sie die Unpivot Transformation auswählen und mit der rechten Maus im Kontextmenü den Punkt Show Advanced Editor... auswählen. Auf dem Tabellenblatt Input Columns finden Sie unten die ausführliche Fehlermeldung.

Das Ergebnis der Transformation sieht so aus:

Wert;W1;Fname;email;Firma;W2
K1;5;willfried;willfried@wfaerber.de;faerber;6
K2;7;willfried;willfried@wfaerber.de;faerber;8
K3;;willfried;willfried@wfaerber.de;faerber;
K1;10;sebastian;sf@wfaerber@de;müller;20
K2;30;sebastian;sf@wfaerber@de;müller;40
K3;50;sebastian;sf@wfaerber@de;müller;60

Auch hier fällt wieder auf, dass die Reihenfolge der Spalten nicht der Reihenfolge entspricht, die wir erwarten würden.

Die Unpivot Transformation kann nicht dynamisch über Expressions konfiguriert werden.

Hier können Sie die zwei Demopakete einschließlich der Eingabedatei herunterladen.

Bitte passen Sie die Connection Manager an Ihre Umgebung an.

Komponentenindex:

• Datenflusstask
• Flatfilequelle
• UnPivot Transformation
• Flatfileziel