Mit MSSQL eine View erstellen

[dnZz]

Hallo, folgende Aufgabenstellung:

Ich habe in einer DB 2 Tabellen die wie folgt aufgebaut sind.

1. Tabelle Kunden mit den Feldern ID, Kunden_ID, Kundenname

Beispiel
ID= 1 Kunden_ID= 1 Kundenname= Test1
ID= 2 Kunden_ID= 2 Kundenname= Test2

2. Tabelle Rufnummern mit den Feldern ID, Kunden_ID, Rufnummer

Beispiel
ID=1 Kunden_ID=1 Rufnummer=123456
ID=2 Kunden_ID=2 Rufnummer=678901
ID=3 Kunden_ID=1 Rufnummer=758493

Ich brauch nun eine View die mir die Daten dann so anzeigt.
(Die Anzahl der Rufnummern die jeder Kunde hat ist unterschiedlich. Es kann vorkommen das ein Kunde 10 Rufnummern hat und ein anderer nur 1 Rufnummer.)

Kunden_ID=1 Rufnummer1=123456 Rufnummer2=758493 Rufnummer3 .... usw.
Kunden_ID=2 Rufnummer1=678901 Rufnummer2=NULL Rufnummer3 .... usw.

Jemand eine Idee wie die Abfrage der View auszusehen hat?

[Exxtreme]

Vielleicht mit sowas?
https://learn.microsoft.com/de-de/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

https://modern-sql.com/de/anwendung/pivot

[Asaraki]

Wenn die Anzahl flexibel sein soll geht das nicht in einem Schritt, ist auch nicht die Idee von SQL

Die view würde dann
K1 t1
K1 t2
K1 t3

liefern. Nicht : k1 t1 t2 etc

Wäre deutlich flexibler. Darstellung als single row in einem zweiten Schritt : wartbarer, schneller und einfacher

[The_Invisible]

Mit STRING_AGG und GROUP könntest es zumindest als String zurückgeben wenns dir so reicht

[Asaraki]

Dann lieber gleich rekursives SQL, aber wenn jemand fragen muss empfehle ich das ausdrücklich nicht. Das tut eventuell nicht so wie man denkt und keiner versteht es ^^

[Picknatari]

SELECT kid.kunden_id, STUFF((SELECT ', ' + krn.telnummer FROM rufnummer krn WHERE krn.kunden_id = kid.kunden_id FOR XML PATH('')), 1, 2, '') AS telnr
FROM kunden kid
Vielleicht?

Funktioniert übrigens....

[Matrix316]

Warum wollen immer alle die Werte nebeneinander haben? Furchtbar, oder? :D

In Excel ist das kein Problem, aber mit SQL ist das halt sau aufwendig über Pivot oder Subselects oder wie auch immer.

Es macht halt keinen Sinn eine Abfrage zu machen wo jede Zeile unterschiedliche Anzahlen von Spalten haben kann. Vor allem, wenn man nicht weiß wie viele es jeweils sind.

[Asaraki]

Weil 80% der Menschen, die mit Daten arbeiten, Daten nicht verstehen sondern sie lesen wie Schrift. Daher instant confusion wenn die Daten im Kopf aggregiert werden müssen...

#dannexceldichhalttotdudepp :D

[Exxtreme]

Es macht halt keinen Sinn eine Abfrage zu machen wo jede Zeile unterschiedliche Anzahlen von Spalten haben kann. Vor allem, wenn man nicht weiß wie viele es jeweils sind.

Das ist halt der Unterschied zwischen einem IT-ler und jemanden, der die Daten nebeneinander will, weil er das schon immer so gemacht hat. Komm hier nicht mit Logik. ;D Und dass Micros~1 die PIVOT-Funktion eingebaut hat, ist sehr wohl ein Zeichen, dass größerer Bedarf danach besteht.

[Asaraki]

ABER DATEN SIND PER SE NICHT HORIZONTAL! AAAAAAAAAAAAH

;)

Nene, völlig einverstanden. Das ist halt "intuitives lesen von Daten" vs "Daten lesen wie sie sind". Ist nicht schlimm, nervt aber ab und zu :D

[nairune]

Ne SQL View ist halt nicht für dummies zum schön angucken.
Kannst es ja mit Semikolons in eine einzelne Spalte verketten, als CSV exportieren und in Excel öffnen, damit es schön aussieht.

[Matrix316]

Die Leute sollen halt mal lernen Excel zu bedienen. Ich hab da auch schon Klimmzüge gemacht um im SQL Spalten horizontal anzuordnen mit verrückten Subselects, aber es ist doch eigentlich relativ einfach eine normale Abfrage in Excel zu transponieren oder über Pivot sich da was zu basteln. =)

EDIT: Man könnte vielleicht eine Stored Procedure nehmen und darin die Abfrage zusammenbauen und dann in eine Zwischentabelle Speichern und dann einen View einfach darauf machen. :) Je nach dem wie aktuell die Abfrage sein soll, muss man halt die SP so und so oft laufen lassen. :D

Alternativ ein Konsolenprogramm mit Visual Studio basteln wo man einen Report als CSV oder so generiert und dann verschickt. Das ist auch noch einfacher als in SQL.

Ist die Frage wie Live das Ergebnis sein soll.

[Picknatari]

Wenn man schon fragt, sollte auch irgendwann mal ein Feedback kommen. Unhöflich.

[Matrix316]

Wenn man schon fragt, sollte auch irgendwann mal ein Feedback kommen. Unhöflich.
Ich bin zwar nicht der Threadersteller, aber deine Variante funktioniert - für eine Nummer. Wenn du mehrere Nummern hast, könnte es einen Fehler geben von wegen: Die Unterabfrage hat mehr als einen Wert zurückgegeben.

Wenn es nicht schlimm ist, wenn die Nummern untereinander stehen kann man am einfachsten einen Join machen:


select a.Kunden_ID,b.Rufnummer from Kunden a inner join Rufnummern b on a.Kunden_ID = b.Kunden_ID order by a.Kunden_ID,b.Rufnummer


Statt inner join geht auch left join, wenn man z.B. Kunden hat, die keine Nummern haben. Die fallen beim inner join weg. Beim outer join bleiben die da und die Nummer ist halt leer.

Das ganze nebeneinander zu haben ist wie gesagt ziemlich tricky, vor allem, wenn man nicht weiß wie viele Nummern ein Kunde haben kann. Wenn es eine Maximalzahl gibt, kann man das mit Subselects oder Pivot machen. Macht die Abfrage aber nicht übersichtlicher.

EDIT: Alleine für zwei Nummern wird es schon wild. Das könnte so aussehen:


select Kunden_ID,
(select top 1 Rufnummer from Rufnummern where Kunden_ID = a.Kunden_ID order by id asc) as Rufnummer_1,
(select top 1 Rufnummer from Rufnummern where id in (select top 2 ID from Rufnummern where Kunden_ID = a.Kunden_ID order by id desc)) as Rufnummer_2,
.
.
.
from Kunden a


Oder so ähnlich. Ich bin gerade nicht am Arbeitsrechner, sonst könnte ichs mal testen. ;)

[Picknatari]

Ich bin zwar nicht der Threadersteller, aber deine Variante funktioniert - für eine Nummer. Wenn du mehrere Nummern hast, könnte es einen Fehler geben von wegen: Die Unterabfrage hat mehr als einen Wert zurückgegeben.

Wenn es nicht schlimm ist, wenn die Nummern untereinander stehen kann man am einfachsten einen Join machen:


select a.Kunden_ID,b.Rufnummer from Kunden a inner join Rufnummern b on a.Kunden_ID = b.Kunden_ID order by a.Kunden_ID,b.Rufnummer
Statt inner join geht auch left join, wenn man z.B. Kunden hat, die keine Nummern haben. Die fallen beim inner join weg. Beim outer join bleiben die da und die Nummer ist halt leer.

Das ganze nebeneinander zu haben ist wie gesagt ziemlich tricky, vor allem, wenn man nicht weiß wie viele Nummern ein Kunde haben kann. Wenn es eine Maximalzahl gibt, kann man das mit Subselects oder Pivot machen. Macht die Abfrage aber nicht übersichtlicher.

EDIT: Alleine für zwei Nummern wird es schon wild. Das könnte so aussehen:


select Kunden_ID,
(select top 1 Rufnummer from Rufnummern where Kunden_ID = a.Kunden_ID order by id asc) as Rufnummer_1,
(select top 1 Rufnummer from Rufnummern where id in (select top 2 ID from Rufnummern where Kunden_ID = a.Kunden_ID order by id desc)) as Rufnummer_2,
.
.
.
from Kunden a
Oder so ähnlich. Ich bin gerade nicht am Arbeitsrechner, sonst könnte ichs mal testen. ;)


Ne, da kommen alle raus. Hab letztens das Konstrukt wieder mal genutzt. Wichtig ist, dass das die Anzahl der Zeichen, welche im Hochkomma stehen, dem rot markierten Parameter entsprechen müssen.



SELECT kid.kunden_id, STUFF((SELECT ', ' + krn.telnummer FROM rufnummer krn WHERE krn.kunden_id = kid.kunden_id FOR XML PATH('')), 1, 2, '') AS telnr
FROM kunden kid

[Matrix316]

Ne, da kommen alle raus. Hab letztens das Konstrukt wieder mal genutzt. Wichtig ist, dass das die Anzahl der Zeichen, welche im Hochkomma stehen, dem rot markierten Parameter entsprechen müssen.



SELECT kid.kunden_id, STUFF((SELECT ', ' + krn.telnummer FROM rufnummer krn WHERE krn.kunden_id = kid.kunden_id FOR XML PATH('')), 1, 2, '') AS telnr
FROM kunden kid

Hmmmmm, das muss ich dann doch mal probieren. Wobei man will bei SQL Abfragen nicht unbedingt XML Code haben. Das macht ja dieses FOR XML da oder so ähnlich. Vielleicht ist das dann doch ganz praktisch.

[Picknatari]

Hmmmmm, das muss ich dann doch mal probieren. Wobei man will bei SQL Abfragen nicht unbedingt XML Code haben. Das macht ja dieses FOR XML da oder so ähnlich. Vielleicht ist das dann doch ganz praktisch.


Ne, wenn die Abfrage richtig ist, kommt da kein XML raus. Sieht aus wie das Ergebnis einer stinknormalen Query.

[Matrix316]

Ne, wenn die Abfrage richtig ist, kommt da kein XML raus. Sieht aus wie das Ergebnis einer stinknormalen Query.

Also bei mir kommt das mit XML Tags raus. So ähnlich wie hier (sind nur Beispieldaten):

Kunden, Rufnummer
1, ufnummerID>12345</RufnummerID><RufnummerID>12346</RufnummerID>
2, ufnummerID>12347</RufnummerID>
3, ufnummerID>12348</RufnummerID>

Wenn man XML braucht ist das schon praktisch. Aber die ersten zwei Ziffern fehlen in der Nummernspalte. :)

[Picknatari]

SELECT kid.adrnrges,kid.name , STUFF((SELECT ', ' + krn.kontakt FROM adrKontakte krn WHERE krn.adrnrges = kid.adrnrges FOR XML PATH('')), 1, 2, '') AS telnr
FROM adradressen kid


Da machste was falsch. Nebenbei: MS SQL 2022

[Matrix316]

Ahhh ich hatte beim FOR XML PATH das ('') vergessen. :)

Jo funktioniert. Wobei du halt die Nummern dann alle jeweils in einer Spalte hast und nicht pro Spalte eine. Wobei das den meisten am Endw wahrscheinlich egal sein wird. ;)

Aber danke für den Tipp, das kann ganz nützlich sein.

EDIT: ich hatte nicht hinten das ('') vergessen sondern vorne beim Select das ', ' - weil ich beim Test ein int Feld verwendet hatte.

[Asaraki]

Naja, kann man machen, aber ist a) unflexibel und b) nicht zuverlässig.
Ist halt sowieso schlechtes Datendesign :D Welche Rufnummer ist welche? Die sind ja nicht geordnet, kannst also auch nicht annehmen, dass die erste die richtige ist, das ist je nach Reorgpattern nämlich die Älteste etc...

Wenn man sowas wirklich will - und das sollte man nicht, sondern das wie schon erwähnt in zwei Schritten tun - müsste man erstmal das Design anpassen um überhaupt eine schlaue Abfrage zu ermöglichen und DANN kommst du mit so einem 'abuse script' nicht mehr weit.

Aber ja, ist für mich ja nichts Neues, dass die meisten ihre eigenen Daten kaum verstehen und dann mit einem SQL irgendwas hinbiegen und sich 2 Jahre später wundern, warum die Kunden die falschen Dinge auf ihren Rechnungen haben ^^

Richtige Lösung : Korrektes Design und dann wenn unbedingt nötig ein rekursives Script darüber, dann hat man auch die volle Kontrolle und kann das auch korrekt testen. Never run untested scripts on a live database boys ;)

[Picknatari]

Naja, kann man machen, aber ist a) unflexibel und b) nicht zuverlässig.
Ist halt sowieso schlechtes Datendesign :D Welche Rufnummer ist welche? Die sind ja nicht geordnet, kannst also auch nicht annehmen, dass die erste die richtige ist, das ist je nach Reorgpattern nämlich die Älteste etc...

Wenn man sowas wirklich will - und das sollte man nicht, sondern das wie schon erwähnt in zwei Schritten tun - müsste man erstmal das Design anpassen um überhaupt eine schlaue Abfrage zu ermöglichen und DANN kommst du mit so einem 'abuse script' nicht mehr weit.

Aber ja, ist für mich ja nichts Neues, dass die meisten ihre eigenen Daten kaum verstehen und dann mit einem SQL irgendwas hinbiegen und sich 2 Jahre später wundern, warum die Kunden die falschen Dinge auf ihren Rechnungen haben ^^

Richtige Lösung : Korrektes Design und dann wenn unbedingt nötig ein rekursives Script darüber, dann hat man auch die volle Kontrolle und kann das auch korrekt testen. Never run untested scripts on a live database boys ;)


Wir hatten in meiner ersten Firma mal Externe angeheuert, die wussten auch, was alles besser geht. Nur ne Lösung haben sie nicht geliefert.

[Asaraki]

Ich zeige nur Risiken auf und warum ich hier keine Lösung präsentiere habe ich erklärt. Ein nicht verstandenes rekursives Script auf deiner Cloud DB kann schnell viel Geld kosten :)

Ich habe nichts dagegen zu basteln, aber wenn hier Leute dann das Script kopieren und was falsches passiert wollen wir ja keine Verantwortung haben :D

Alles gut, sorry falls das so rüberkam als wollte ich dich belehren. War nicht meine Absicht