bessere SQL Abfrage

[LoZe]

Hallo ich habe eine MSSQL Tabelle in diesen Format (kann nicht geändert werden)

Code:

[Objekt]  [Atribut] [Value]
Müller    City      City1     
Müller    Street    Street1   
Müller    Num       1234      
Mayer     City      City2     
Mayer     Street    Street2   
Mayer     Num       4321

Was ist der Beste Weg um dies zu bekommen?

Code:

[Objekt]  [Street]  [City]    [Number]
Mayer     Street2   City2     4321
Müller    Street1   City1     1234

bisher löse ich es so:

Code:

DECLARE @allObj TABLE ( Objekt NVARCHAR(12)) 
INSERT @allObj
SELECT Objekt FROM Table_1_Test group by Objekt

SELECT Tab2.Objekt,
   (SELECT [Value] FROM [Table_1_Test] WHERE [Objekt] = Tab2.Objekt AND [Atribut] = 'Street') AS [Street],
   (SELECT [Value] FROM [Table_1_Test] WHERE [Objekt] = Tab2.Objekt AND [Atribut] = 'City') AS [City],
   (SELECT [Value] FROM [Table_1_Test] WHERE [Objekt] = Tab2.Objekt AND [Atribut] = 'Num') AS [Number]
From @allObj as Tab2

hat jemand ne bessere Möglichkeit?

[DeddyH]

Klappt das hier?

SQL-Code:

SELECT
  T1.Objekt AS Objekt,
  T2.Value AS Street,
  T3.Value AS City,
  T4.Value AS Number
FROM
  Table_1_Test T1
  LEFT JOIN Table_1_Test T2 ON T2.Objekt = T1.Objekt AND T2.Attribut = 'Street'
  LEFT JOIN Table_1_Test T3 ON T3.Objekt = T1.Objekt AND T3.Attribut = 'City'
  LEFT JOIN Table_1_Test T4 ON T4.Objekt = T1.Objekt AND T4.Attribut = 'Num'
GROUP BY
  T1.Objekt, T2.Value, T3.Value, T4.Value

[LoZe]

Leider nicht

Code:

Die Table_1_Test.Value-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

so geht es:

Code:

SELECT
  distinct T1.Objekt AS Objekt,
  T2.Value AS Street,
  T3.Value AS City,
  T4.Value AS Number
FROM
  Table_1_Test T1
  LEFT JOIN Table_1_Test T2 ON T2.Objekt = T1.Objekt AND T2.Atribut = 'Street'
  LEFT JOIN Table_1_Test T3 ON T3.Objekt = T1.Objekt AND T3.Atribut = 'City'
  LEFT JOIN Table_1_Test T4 ON T4.Objekt = T1.Objekt AND T4.Atribut = 'Num'

vielen dank für den Tipp

[DeddyH]

Das war mir auch noch nachträglich eingefallen, daher mein Edit. Wobei das alles irgendwie suboptimal ist, das liegt aber eher an der Struktur.

[LoZe]

die Struktur ist leider vorgegeben und kann nicht geändert werden :-(
aber jetzt läuft es mal um einiges schneller als mit meiner Variante.

[jobo]

wenn die Ausgangsdaten per Order By über Objekt, Attribut (was besseres scheint es ja nicht zu geben) in eine definierte Reihenfolge gebracht werden können und garantiert immer alle 3 Elemente drin sind, könnte man es mit Windows Functions und Lag/Lead versuchen.
Damit käme man ohne Joins aus und hätte wahrscheinlich nur einen (einzigen) Single Scan über die Table. Das würde sich bei größeren Datenmengen vielleicht lohnen.

[himitsu]

In einigen DBMS gibt es Features oder nachrüstbare Funktionen für eine POVIT-Abfrage,
in welcher man quasi eine Tabelle drehen kann, also aus Zeilen werden Spalten oder andersrum.

https://modern-sql.com/de/anwendung/pivot
z.B. https://www.postgresql.org/docs/9.1/tablefunc.html

Aber ja, oftmals macht man das wohl über Joins bzw. Sub-Selects oder bissl gewöhnungbedürftiger mit GroupBy+Aggregate+Filter.

[LoZe]

Ich habe die Struktur ja nur Beispielhaft dargestellt.

es sind 1 Millionen Reihen an Datensätzen,
leider sind nicht immer alle Attribute gegeben,
und bei einigen Attributen (3 Stück) können auch mehrere Reihen da sein.

Ich habe im Moment jeden Tag ein Task laufen der mir die Daten in einer Anderen Tabelle in einem besseres Format Ablegt.
dort sind es dann nur noch ca 250 Reihen mit ca 20 Spalten.

diesen Taks will ich aber beschleunigen da dieser mittlerweile ca. 30 Minuten braucht

[generic]

Die Idee von himitsu mit dem PIVOT finde ich auch nicht so schlecht.

Ist der Primärschlüssel auf [Objekt] und [Attribut] ggf. sogar Clustered ?
Evtl. kann man mit einer StoredProcedure/Tablefunction und einen Cursor da als "Tabellescan" schneller durchgehen und die Datensätze zusammenbauen.

Du kannst auch mal prüfen ob ein columnstore Index etwas für dich ist.

Ansonsten bau Trigger auf Tabelle, welche deine LIVE aktualisiert.

[LoZe]

Ja ich werde mir das mal anschauen, danke mal für die infos

[himitsu]

Klar, wenn sich das SELECT so weit optimieren lässt, das die Daten schnell zusammengesucht sind, dann wäre es schöner. (vielleicht kann man dann sogar die Kopie/Cache lostwerden und direkt)

* Join statt SubSelect
* mit passenden Indize auf allen wichtigen Spalten
* eventuell vorhandene und optimiertere Fertiglösungen nutzen
* ...



Wir hatten sowas früher als Cache in einem DataSnap-Server.
Eine Querykomponente holt die Daten, wenn fertig wurde das schnell in eine MemoryTable kopiert (bzw. dieses Query mit dem Ausgabequery getauscht) und in der zwischenzeit konnten die Clients multithreaded auf die fertige Kopie zugreifen.

Und inzwischen wurde es auf Materialized-Views umgestellt (Postgres),
also auf vorberechnete Views, wo alle paar Minuten unser Server nur noch den Refresh anstößt.
Ist in etwa sowas wie eine virtuelle Tabelle, welche regelmäßig über einen View bzw. StoredProc gefüllt/aktualisiert wird.