Hilfe bei Pivot - oder Transformation von Zeilen in Spalten

Firelady1988

Firelady1988

Benutzer
Beiträge
10
Hallo liebe Leute,

als Fortgeschrittener Anfänger (gibts sowas ? :-D) bin ich auf eine Fragestellung gestoßen wo ich leider nicht mehr weiter weiß.
Ich muss Produktionsdaten aufbereiten. Diese sammel ich mir in einer View zusammen, am Ende müssten die Daten aber leider "gedreht" werden, da die weiterverarbeitende Software die Transformation von Zeilen in Spalten leider nicht kann.

View_Prozessdaten
TankWertBezeichnungMaterial1Material 2
115Tomatenmark711712
211Chili-Soße715153
313Käsesoße586578
49Knoblauchsoße899145



1234
Wert1511139
BezeichnungTomatenmarkChili-SoßeKäsesoßeKnoblauchsoße
Material1711715586899
Material2712153578145

Der Befehl bringt leider nicht die erwünschte Ausgabe. Ich hatte es in einem meiner unzähligen Versuche hinbekommen, dass wenigstens der Wert als Zeile angezeigt wurde, aber eben nur eine einzige Zeile.

SELECT [Wert],[Bezeichnung],[Material1],[Material2]
FROM VIEW_Prozessdaten
PIVOT (AVG([Wert]) for [Tank] in ([1], [2], [3], [4])) as pvt

Kann mir bitte jemand helfen?
 
Werbung:
Ich hasse PIVOT, ist was persönliches...

Auf die Schnelle würde ich sagen AVG([Wert]) ist falsch, da muss nur [Wert] rein, sonst wird ja was aggregiert.
 
jetzt noch mal, erste Version war falsch ...

Code: 
postgres=# select * from firelady ;
 tank | wert |  bez   | m1  | m2  
------+------+--------+-----+-----
    1 |   15 | tomate | 711 | 712
    2 |   11 | chili  | 715 | 153
    3 |   13 | käse   | 586 | 578
    4 |    9 | knobi  | 899 | 145
(4 rows)

postgres=# with foo as (select array_agg(wert::text) as c1, array_agg(bez) as c2, array_agg(m1::text) as c3, array_agg(m2::text) as c4 from firelady) select 'Wert' as c0, c1[1], c1[2], c1[3], c1[4] from foo union all select
'Bezeichnung', c2[1], c2[2], c2[3],c2[4] from foo union all select 'Material1', c3[1], c3[2], c3[3], c3[4] from foo union all select 'Material2', c4[1], c4[2], c4[3], c4[4] from foo ;
     c0      |   c1   |  c1   |  c1  |  c1   
-------------+--------+-------+------+-------
 Wert        | 15     | 11    | 13   | 9
 Bezeichnung | tomate | chili | käse | knobi
 Material1   | 711    | 715   | 586  | 899
 Material2   | 712    | 153   | 578  | 145
(4 rows)

postgres=#
 
akretschmer schrieb:
jetzt noch mal, erste Version war falsch ...

Code: 
postgres=# select * from firelady ;
 tank | wert |  bez   | m1  | m2 
------+------+--------+-----+-----
    1 |   15 | tomate | 711 | 712
    2 |   11 | chili  | 715 | 153
    3 |   13 | käse   | 586 | 578
    4 |    9 | knobi  | 899 | 145
(4 rows)

postgres=# with foo as (select array_agg(wert::text) as c1, array_agg(bez) as c2, array_agg(m1::text) as c3, array_agg(m2::text) as c4 from firelady) select 'Wert' as c0, c1[1], c1[2], c1[3], c1[4] from foo union all select
'Bezeichnung', c2[1], c2[2], c2[3],c2[4] from foo union all select 'Material1', c3[1], c3[2], c3[3], c3[4] from foo union all select 'Material2', c4[1], c4[2], c4[3], c4[4] from foo ;
     c0      |   c1   |  c1   |  c1  |  c1  
-------------+--------+-------+------+-------
 Wert        | 15     | 11    | 13   | 9
 Bezeichnung | tomate | chili | käse | knobi
 Material1   | 711    | 715   | 586  | 899
 Material2   | 712    | 153   | 578  | 145
(4 rows)

postgres=#
Zum Vergrößern anklicken....

Hallo akretschmer, danke für den Ansatz. Leider hilft mir das auf dem SQL Server nicht weiter, weil die Funktion array_agg nicht von SQL Server unterstützt wird. Leider bin ich dafür noch zu viel Anfänger, dass ich mir das nicht auf meine Belange umbauen kann :-(
 
Hilft den #2?
Code: 
SELECT [Wert],[Bezeichnung],[Material1],[Material2]
FROM VIEW_Prozessdaten
PIVOT ([Wert] for [Tank] in ([1], [2], [3], [4])) as pvt
 
Wie dynamisch muss das denn sein? Sprich, wie viele Tanks gibt oder kann es geben? Und kommt ein Tank mehrmals als Zeile vor oder immer genau einmal?
 
Firelady1988 schrieb:
Es muss überhaupt nicht dynamisch sein.
Zum Vergrößern anklicken....
Wahrscheinlich geht das auch toller, aber so irgendwie müsste es jedenfalls gehen. Vielleicht kann man die Typcasts besser machen oder auch den ganzen Aufbau:

Code: 
SELECT 'Bezeichnung' AS Kategorie,
       [1], [2], [3], [4]
FROM
  (SELECT t, b
   FROM daten) d pivot (max(b) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'Wert' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar) AS [2],
                 CAST ([3] AS varchar) AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, w
   FROM daten) d pivot (max(w) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'm1' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar)AS [2],
                 CAST ([3] AS varchar)AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, m1
   FROM daten) d pivot (max(m1) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'm2' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar)AS [2],
                 CAST ([3] AS varchar)AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, m2
   FROM daten) d pivot (max(m2) FOR t in ([1], [2], [3], [4])) AS p
 
dabadepdu schrieb:
Wahrscheinlich geht das auch toller, aber so irgendwie müsste es jedenfalls gehen. Vielleicht kann man die Typcasts besser machen oder auch den ganzen Aufbau:

Code: 
SELECT 'Bezeichnung' AS Kategorie,
       [1], [2], [3], [4]
FROM
  (SELECT t, b
   FROM daten) d pivot (max(b) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'Wert' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar) AS [2],
                 CAST ([3] AS varchar) AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, w
   FROM daten) d pivot (max(w) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'm1' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar)AS [2],
                 CAST ([3] AS varchar)AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, m1
   FROM daten) d pivot (max(m1) FOR t in ([1], [2], [3], [4])) AS p
UNION ALL
SELECT 'm2' AS Kategorie,
       CAST ([1] AS varchar) AS [1],
            CAST ([2] AS varchar)AS [2],
                 CAST ([3] AS varchar)AS [3],
                      CAST ([4] AS varchar) AS [4]
FROM
  (SELECT t, m2
   FROM daten) d pivot (max(m2) FOR t in ([1], [2], [3], [4])) AS p
Zum Vergrößern anklicken....
Danke, dabadepdu das war mal wieder ein richtig guter Gedankenanstoß 😃
Ich habe es jetzt vielleicht etwas anders gemacht, auf jeden Fall bringt es schon mal genau das richtige Ergebnis.
Eine Hürde gibt es aber noch.... Der Text!!!
Es kommt immer:
"Fehler beim Konvertieren des nvarchar-Werts "Tomate" in den int-Datentyp."

Daraufhin habe ich die ganzen CONVERT eingefügt, das brachte leider auch nicht den Erfolg :-(

Hast du da vielleicht noch den Tipp woher das kommt und wie ich es behebe?


SELECT
Cast('Wert'AS Nvarchar(255)) as Eigenschaft,
MAX(CASE WHEN [Tank] = 1 THEN CONVERT(VARCHAR(255),[Wert]) END) AS [1],
MAX(CASE WHEN [Tank]= 2 THEN CONVERT(VARCHAR(255), [Wert]) END) AS [2],
MAX(CASE WHEN [Tank] = 3 THEN CONVERT(VARCHAR(255), [Wert]) END) AS [3]
FROM [View_Prozessdaten]
UNION
SELECT
CAST('Bezeichnung' AS NVARCHAR(255)) AS Eigenschaft,
MAX(CASE WHEN [Tank] = 1 THEN [Bezeichnung] END) AS [1],
MAX(CASE WHEN [Tank] = 2 THEN [Bezeichnung] END) AS [2],
MAX(CASE WHEN [Tank] = 3 THEN [Bezeichnung] END) AS [3]
FROM [View_Prozessdaten]
UNION
SELECT
CAST('Material1' AS NVARCHAR(255)) AS Eigenschaft ,
MAX(CASE WHEN [Tank] = 1 THEN [Material1] END) AS [1],
MAX(CASE WHEN [Tank] = 2 THEN [Material1] END) AS [2],
MAX(CASE WHEN [Tank] = 3 THEN [Material1] END) AS [3]
FROM [View_Prozessdaten]
UNION
SELECT
CAST('Material2' AS NVARCHAR(255)) AS Eigenschaft ,
MAX(CASE WHEN [Tank] = 1 THEN CONVERT(VARCHAR(255), [Material2]) END) AS [1],
MAX(CASE WHEN [Tank] = 2 THEN CONVERT(VARCHAR(255), [Material2]) END) AS [2],
MAX(CASE WHEN [Tank] = 3 THEN CONVERT(VARCHAR(255), [Material2]) END) AS [3]
FROM [View_Prozessdaten]
 
Firelady1988 schrieb:
Hast du da vielleicht noch den Tipp woher das kommt und wie ich es behebe?
Zum Vergrößern anklicken....
Nicht wirklich.
Ich sehe in Deinem gezeigten Statement nicht, wo ein Int Wert als Cast Ziel überhaupt angefordert wird.
"Wert" ist die erste Spalte im Union, die Du verarbeitest und nach Varchar konvertierst, damit der Typ für 1-3 vorgegeben.
"Bezeichung" ist sowieso varchar und muss nicht convertiert werden.
"Material1" sieht nach int aus und Du konvertierst nicht. Passiert vielleicht implizit, wäre ja möglich.
"Material2" sieht genauso nach int aus und Du konvertierst es.

Vielleicht ist das Statement, was Du gepostet hast nicht das, was d en Fehler produziert.
 
dabadepdu schrieb:
Nicht wirklich.
Ich sehe in Deinem gezeigten Statement nicht, wo ein Int Wert als Cast Ziel überhaupt angefordert wird.
"Wert" ist die erste Spalte im Union, die Du verarbeitest und nach Varchar konvertierst, damit der Typ für 1-3 vorgegeben.
"Bezeichung" ist sowieso varchar und muss nicht convertiert werden.
"Material1" sieht nach int aus und Du konvertierst nicht. Passiert vielleicht implizit, wäre ja möglich.
"Material2" sieht genauso nach int aus und Du konvertierst es.

Vielleicht ist das Statement, was Du gepostet hast nicht das, was d en Fehler produziert.
Zum Vergrößern anklicken....
Doch, dass kommt tatsächlich aus der Bezeichnung. Ich habe diesen Bereich rausgenommen und der Code läuft perfekt durch.
Ich habe festgestellt, dass er die neuen Spalten Tanks als "int" anlegt, da wird dann der Fehler passieren weil er den Text nicht verarbeiten kann.

Wie bekomme ich den Tanks einen anderen Datentyp zugewiesen?
 
Werbung:
Firelady1988 schrieb:
Wie bekomme ich den Tanks einen anderen Datentyp zugewiesen?
Zum Vergrößern anklicken....
Es gibt keine Spalte Tanks in Deinem Statement. Ich nehme an Du meinst die Spalten, die nach den individuellen Tanks benannt sind.

Ich rate mal oder sagen wir so, ich würde es nie absichtlich so "bequem" machen. Schon gar nicht bei einem Union, wo man die Typen einhalten muss.
Lange Erklärung:
Du hast in allen 3 Tanks Spalten die Case Statements. Das könnte eine Copy / Paste Lösung sein oder Du hast verstanden, was da passiert. Du erzeugst fallweise (oder spaltenweise) ein Ergebnis, das mehrzeilig ist und kannst es anschließend per Aggregatfunktion (max) zu einer Zeile zusammenfassen. Die Case Formulierung ist in Deinem Statement etwas schlampig / sparsam. Case ist außerdem etwas "gemein", weil es durch beliebige Fallunterscheidung mit individuellen Ausgabewerten auch individuelle AusgabeTYPEN produzieren kann:
Code: 
..case when 'heute' then getdate()
case when 'morgen' then 1
case when 'jetzt' then jetzt
case when 'mirdochegal' then '42'
...
Würde so kein Mensch machen, aber Du bist nah dran. Obwohl Du die Konvertierung machst.
In Deinem Statement fehlt der ELSE Teil- wie in meinem Beispiel. Er findet aber statt! "da kommt ja sowieso nichts raus", könnte man meinen, ist ja auch so, man produziert ein NULL Ergebnis. Aber von welchem Typ?
Hättest Du eine Datenbank entwickelt und das Case When Statement implementiert, welchen Typ hättest Du dem Ergebnis verpasst?

kurze Antwort:
Versuch mal, ein Else dazu mit Convert oder direkt ein Convert um das ganze Case Ergebnis, eben nicht nur im Then Teil.

Und unabhängig davon ob das die Lösung ist.
Man sollte sich nie auf implizite Typkonvertierung verlassen. (Und man muss vielleicht ein wenig üben, um es nicht unabsichtlich zu machen)
 
Zurück
Oben