[SQL] Boolsche Matrix -> Liste von Paaren

[Medium]

Huhu DP!

Ich habe eine antike Tabelle, in der mögliche Quellen und Ziele einer Produktionsanlage einander zugeordnet sind. Diese Tabelle wurde damals (leider) als Matrix ausgeführt, ich brauche aber für meine Modernisierung der Anlage nun alle Paare, in denen in der Matrix eine "1" steht. Beispiel:

Code:

 SR | T1 | T2 | T3 | T4 | T5
  1 |  0 |  0 |  0 |  0 |  0 
  2 |  0 |  1 |  0 |  0 |  0 
  3 |  0 |  0 |  0 |  0 |  0 
  4 |  0 |  0 |  1 |  0 |  0 
  5 |  0 |  0 |  1 |  0 |  0

Soll werden zu: (id=auto_inc)

Code:

 id | SR | TG
  1 |  2 |  2
  2 |  4 |  3
  3 |  5 |  3

Das von Hand zu machen wäre etwas Fehleranfällig, da die Tabelle ~50 Spalten und ~100 Zeilen hat, und es insgesamt in rund 300 Paare resultieren dürfte. Kann ich das irgendwie direkt als SQL Statement verhackstücken, oder muss ich mir dafür ein Töölchen bauen? Danke vorab!

[jobo]

select sr,
case when t1=1 then t1
case when t2=1 then t2
else 'Error'
end
as Tg new
from ...

Case Syntax entsprechend maria/mysql weiß ich nicht genau auswendig.

Das Statement kann man notfalls in Excel mit einer Zellenfunktion "generieren" für so viel Spalten, wie benötigt.

[Frickler]

Enthält jede Zeile genau ein Matrixelement? Dann kannst Du auch sowas machen wie

Code:

SELECT SR, T1*1 + T2*2 + T3*3 + T4*4 + ... + T50*50 AS TG FROM matrix
WHERE TG <> 0

Wenn die leeren Einträge NULL statt 0 sind, muss jeder Tx Term ersetzt werden durch COALESCE(Tx, 0)*x.

[Delphi.Narium]

ungefähr sowas?

SQL-Code:

insert into neue_tabelle (sr, tg)
select sr, 
case 
  when t1 = 1 then 1
  when t2 = 1 then 2
  when t3 = 1 then 3
  when t4 = 1 then 4
  when t5 = 1 then 5
end as tg
from alte_tabelle 
where t1 + t2 + t3 + t4 + t5 > 0
order by 1

ID als Auto-Inc wird ja (hoffentlich) von der DB automatisch vergeben.

[Medium]

Hmmm, erstmal vielen Dank!

Die Varianten mittels case würden wohl für eine 50x100 Tabelle aufwändiger zu konstruieren als ein Progrämmchen, dass das macht. Es sind auch leider Zeilen und Spalten dabei (also egal ob transponiert oder nicht) die mehr als nur eine 1 haben. Hatte gehofft, dass es dazu eine einfache Standardlösung gibt, die ich einfach nur nicht kenne. Klingt erst mal nach einem "normalen" Problem vor dem viele hätten stehen können :) Ich glaube, ich gieße dann lieber einen kleinen 20-Zeiler in Delphi. Soll ja auch nur einmalig sein. Danke euch dennoch!

[p80286]

Die Delphi (oder Excel) -Lösung würde ich auch bevorzugen. Denn das könnte man bei solchen Problemen immer wieder mal nutzen, während die reine DB-Lösung zu unflexibel ist.
(die Feldnamen sollte die DB aber ausspucken können, da ist nichts mehr mit vertippen!)

Gruß
K-H

[jobo]

Tja, case when hätte es ja im Prinzip getan.
Aber bei Mehrfachnennungen wird's schwierig.

Eine bessere Datenbank wäre da auch hilfreich. Dann wäre es wohl bei einem SQL Statement geblieben. Bei 50 Spalten und 100 Zeilen (und Delphikenntnis) ist es aber auch wurscht.

[Medium]

Bessere DB? Gibt's da eine für ähnliche Preise, so einfach zu installieren und konfigurieren, ausreichend für ~5 Clients ohne besondere Ansprüche an Geschwindigkeit und/oder Skalierbarkeit? Immer im Hinterkopf dabei, dass wir hier keinen wirklichen DBMS Spezi haben?

[jobo]

Postgres ist funktional sehr nahe an Oracle, teilweise vielleicht weiter, es bietet starke Indizierungsvarianten.
Es bietet eine sehr gute Programmierbarkeit (StoredProcedure bzw. Functions), nosql Funktionalität, SQL Anbindung für XML, JSON, JSONB, Arrays, .. Man kann also z.B. aus XML selectieren oder zu XML konvertieren.
Der SQL Standard ist recht weit ausgereift und natürlich gibt es nette Eigenheiten. IdR kann man da mit SQL "coolere" Sachen machen, als mit mysql/maria. Mit der neuen mysql 8er Version hat sich das etwas entschärft, also mysql hat aufgeholt, aber wie das weitergeht, weiß man nicht. Für Kreuztabellen liefert postgres z.B. eine Funktion. Die umgekehrte Form, die für Dich interessant gewesen wäre, ist nicht so straight forward (bei Oracle und MSSQL bspw. > unpivot), aber wahrscheinlich trotzdem ein Statement.
Und es ist sowohl skalierbar und als auch anspruchslos für kleine Installationen. Ein normales Setup ohne große Konfiguration ist kein Hexenwerk, kosten tut es auch nichts.
Was mir nicht so aktuell bekannt ist, sind die Treiberwelten für Delphi, also ADO usw.

[Medium]

Hmm, viele der Funktionen die du nennst brauchen wir aktuell und auch sehr wahrscheinlich künftig nicht, aber klingt dennoch als wäre es wert damit etwas herumzuspielen.

Doof ist nur, dass wir erst kürzlich unser veraltetes UniDAC für D2007 für ein aktuelles MyDAC eingetauscht haben (um Kosten zu sparen und wir all die Jahre nie einen anderen Provider brauchten). Von ADO bekomme ich noch immer böse Träume (vor Ewigkeiten mal mit rumgespielt), oder ich verwechsle das gerade mit ODBC.

Mein Problem mit funktional mächtigen Dingen ist: Oftmals heißt viele Möglichkeiten zu haben dann auch sich mit eben diesen auskennen zu müssen wenn man sein System einigermaßen im Griff haben will. Da die DB für uns aber nur Mittel zum Zweck ist, ein kleiner Teil unseres eigentlichen Geschäfts, bin ich da recht vorsichtig. (Vor allem nachdem ich mal in anderer Sache an einen MSSQL Server ran musste. Da fing mein Latein erst gar nicht an! :stupid:)

Und bis auf solche sehr seltenen Sonderfälle hat uns bei MySQL/MariaDB bisher nie etwas gefehlt. Viel komplexer als ein paar joins über vielleicht 3-4 Tabellen und einfache Filterung und ein wenig Fremdschlüsselei wird's nicht. Nichtmals für stored procedures habe ich bisher wirklich sinnvolle Anwendung gefunden bei uns.