SQL - Mehrere Sätze in einen Satz mit mehreren Spalten

[wti]

Hi @ll,

ich habe mir jetzt die Zähne ausgebissen und hoffe dass jemand von euch eine zündende Idee hat.
Im Internet habe ich keine Lösung gefunden, bzw. ich habe die unten realisierte Lösung gefunden, die aber auf dem System i scheinbar nicht läuft.

Die Anforderung ist eigentlich ganz einfach: ich möchte Namen aus mehreren Zeilen einer Tabelle in separaten Spalten in einer Zeile ausgegeben haben.

Tabelle1:
Nummer1
4711
4712
4713

Tabelle2:
Nummer2   | Name
4711      | Name1
4711      | Name2
4711      | Name3
4711      | Name4
4712      | Name1
4712      | Name2
4713      | Name1
4713      | Name2
4713      | Name3

Ergebnis:
 Nummer1   | Spalte1     | Spalte2      | Spalte3       | Spalte4
 4711      | Name1       | Name2        | Name3         | Name4
 4712      | Name1       | Name2        |               | 
 4713      | Name1       | Name2        | Name3         |

Ich dachte mir in einer Variablen die Nummer zu merken, um festzustellen ob die Nummer wechselt und in der anderen Variablen hochzuzählen der wievielte Satz innerhalb jeder Nummer gelesen wurde. Damit habe ich gleichzeitig die Spalte in der der Name eingestellt werden muss.

CREATE VARIABLE yrn dec
CREATE VARIABLE ystnr dec

select Nummer1 as xxstnr, p.Nummer2, p.Name, yrn, ystnr, RowNum  
       MAX(CASE WHEN p.RowNum=1 THEN p.Name END) as Spalte1,     
       MAX(CASE WHEN p.RowNum=2 THEN p.Name END) as Spalte2,     
       MAX(CASE WHEN p.RowNum=3 THEN p.Name END) as Spalte3,     
       MAX(CASE WHEN p.RowNum=4 THEN p.Name END) as Spalte4      
       from Tabelle1                                             
inner join ( select Nummer2, Name,                               
         set yrn   = case when Nummer2 <> ystnr then 0 end,      
         set ystnr = Nummer2,                                    
         set yrn   = yrn + 1 as RowNum                           
      from Tabelle2                                              
      group by Nummer2, Name                                     
      order by Nummer2, Name )  as p                             
on Nummer1 = p.Nummer2                                           
order by Nummer1

Natürlich freue ich mich auch über eine andere Lösung

Gruß
wti

[andreaspr@aon.at]

Hi wti,

dieses Thema gab es schon ein paar mal. Aber leider gibt es nicht DIE perfekte Lösung dafür.

*) Hierarchical queries

*) Eine eigene UDTF in der du das gewünschte Ergebnis einfach Programmierst.

Ich würde eher das Programmieren empfehlen.
Wenn das SQL zu komplex wird ist das Lesen, die Wartung und jede Änderung sehr mühsam.
Ich gehe da immer nach dem Kosten/Nutzen-Prinzip.

lg Andreas

[wti]

Hi wti,

dieses Thema gab es schon ein paar mal. Aber leider gibt es nicht DIE perfekte Lösung dafür.

*) Hierarchical queries

*) Eine eigene UDTF in der du das gewünschte Ergebnis einfach Programmierst.

Ich würde eher das Programmieren empfehlen.
Wenn das SQL zu komplex wird ist das Lesen, die Wartung und jede Änderung sehr mühsam.
Ich gehe da immer nach dem Kosten/Nutzen-Prinzip.

lg Andreas

Danke für die Info - mit dem Programmieren bin ich bereits gestartet.
Andere Lösungen - die es schon ein paar mal gab - habe ich leider nicht gefunden.
Anderseits ist das SQL in dieser Form nicht besonders komplex. Ich muss ja nur wissen wie ich die Variablen in dem inner join aus den Datei-Feldern belege, bzw. den Zähler manipuliere.

LG
Wolfgang

[B.Hauser]

Das Problem ist, dass bei Tabelle2 eine unbestimmte Anzahl an Werten/Namen angegeben werden kann.
Sofern du die Namen in einzelnen Spalten unterbringen willst, musst Du die maximale Anzahl der vorkommenden Spalten kennen. In einer View müssen alle möglichen Spalten definiert werden.
Sofern dies der Fall sein sollte, kann bei maximal 4 Namens-Spalten die folgende Abfrage verwendet werden:

With x as (Select Row_Number() Over(Partition By Nummer2 Order By Name) as Nr, 
                  Nummer2, Name
             from Table2)
Select Nummer2, Max(Case When Nr = 1 then Name Else '' End) col1,
                Max(Case When Nr = 2 Then Name Else '' End) col2,
                Max(Case When Nr = 3 Then Name Else '' End) col3,
                Max(Case When Nr = 4 Then Name Else '' End) Col4
   From table1 join x on nummer1 = nummer2
   Group By Nummer2;;

Sofern die Anzahl der Vorkommen nicht bekannt ist, oder variieren kann, kannst Du nur alles, wie Andreas vorgeschlagen hat in einer einzigen Spalte über Rekursion oder eine UDF (User Defined Function ausgeben.

Birgitta

[Fuerchau]

Wie immer gibt's natürlich viele Lösungen:

select
key, Name1
,(select Name2 from myfile b where a.key=b.key) name2
,(select Name3 from myfile b where a.key=b.key) name3
,(select Name4 from myfile b where a.key=b.key) name4
: -- je Spalte ein neuer Subselect
from myfile a

[B.Hauser]

@Baldur

Das könnte nur klappen, wenn die Namen immer Name1, Name2, Name3 und Name4 wären, wovon ich allerdings nicht ausgehe.
Des weiteren würde ich von dieser Lösung abraten, da durch die vielen unnötigen Sub-Selects die Performance in die Knie geht.

Birgitta

[BenderD]

@Baldur

Das könnte nur klappen, wenn die Namen immer Name1, Name2, Name3 und Name4 wären, wovon ich allerdings nicht ausgehe.
Des weiteren würde ich von dieser Lösung abraten, da durch die vielen unnötigen Sub-Selects die Performance in die Knie geht.

Birgitta

... den ersten Punkt teile ich (funzt net); den zweiten Punkt (Performance) ordne ich mal in die Rubrik "Wie sich Klein-Erna die Query Engine vorstellt" ein; Subselects werden zumeist in Joins aufgelöst, was auch gut so ist!

D*B

[Fuerchau]

Jo, da habe ich nicht genau hingesehen.

[wti]

Danke Birgitta,

deine Lösung funktioniert wunderbar. Die Anzahl der Namen ist auf 30 beschränkt, was natürlich dazu führt dass 30 CASE-Abfragen benötigt werden.
Ist aber schneller als ein RPG-Programm zu schreiben.

Gewusst wie!

LG
Wolfgang

[B.Hauser]

ordne ich mal in die Rubrik "Wie sich Klein-Erna die Query Engine vorstellt" ein; Subselects werden zumeist in Joins aufgelöst, was auch gut so ist!

Die Query-Engine ist zwar besser geworden und erkennt und konvertiert inzwischen meist solche Konsturkte, ... aber eben nur meist!
Trotzdem ... bei 30 unnötigen Joins sind mindestes 29 zuviel!

Birgitta

[BenderD]

Die Query-Engine ist zwar besser geworden und erkennt und konvertiert inzwischen meist solche Konsturkte, ... aber eben nur meist!
Trotzdem ... bei 30 unnötigen Joins sind mindestes 29 zuviel!

Birgitta

... bezüglich Performance hilft nur messen, alles andere ist reine Kaffeesatzleserei!

[holgerscherer]

... bezüglich Performance hilft nur messen, alles andere ist reine Kaffeesatzleserei!

und dann nicht vergessen, dass sich der Kram je nach CPU-Typ (Power5,6,7,8) und Ressourcen-Ausstattung noch anders verhalten kann als gedacht