[T-SQL/PIVOT] Artikel Existenz in Shops

[Arne Drews]

Hi,

Ich bin gerade geistig etwas umnachtet und danke für eure Denkanstöße.


Was ich habe
In einer MSSQL-Umgebung habe ich zwei hierfür relevante Tabellen ( nur relevante Spalten! ):

Produkte

Artikelnummer

Bezeichnung

WebshopId

Webshop

WebshopId

WebshopName

In der Tabelle Produkte sind die Artikel pro Shop eingetragen, im Folgenden beispielhaft mal mit den Artikelnummer A und B bezeichnet.
Die Tabelle Webshop repräsentiert logischer Weise die einzelnen Webshops, fortlaufend numerisch indexiert.


Beispielaufbau

Webshop

WebshopId	WebshopName
1	Shop1
2	Shop2
3	Shop3

Produkte

Artikelnummer	Bezeichnung	WebshopId
A	Artikel EINS	1
A	Artikel EINS	2
A	Artikel EINS	3
B	Artikel ZWEI	2

Mein gewünschtes Ergebnis wäre nun folgendes:

Artikelnummer	Produktbezeichnung	Shop1	Shop2	Shop3
A	Artikel EINS	1	1	1
B	Artikel ZWEI	NULL	1	NULL

Meine Frage dazu
Ich habe wohl schon Auswertungen mit PIVOT gemacht, aber da hatte ich immer ein Aggregat mit drin ( bspw. Umsatz pro Jahr pro Kunde ), was hier nicht der Fall ist.
Vielleicht bin ich mit PIVOT auch falsch aufgestellt? Ich komme aktuell auf keine sinnvolle Idee, das umzusetzen.

Was vielleicht noch wichtig zu erwähnen wäre:
Es können weitere Shops zukommen, was eine gewisse Dynamik in der Query verlangen würde.
Sollte das zu komplex werden, würde mir eine statische Lösung zu dem Fall erstmal helfen.


Für Anregungen bin ich dankbar!
Gruß Arne

[defo]

Interessant, dass das nicht geht bei MSSQL. Welche Version?

Ich vermute, dann muss man wohl in den Apfel beißen und eine unwirksame Aggregatfunktion nehmen, eben max(id) oder so was.
Und dynamisch wird es ja leider mit der PIVOT Anweisung eh nicht.
Sind es also nur 3 Shops, kann man ggF. auch gleich den alten Case When Workaround nehmen.

Aber apropos Dynamic: Es gibt für Oracle/Postgres Verfahren, die die Dynamic auf einem Umweg über XML Verarbeitung erreichen. Sowas kann evtl. auch MSSQL. Also Rohdaten > XML > XML pivot > Konvertierung zurück als Table.

[Arne Drews]

Hi,

Interessant, dass das nicht geht bei MSSQL. Welche Version?

Vielleicht geht es ja, deshalb frage ich ja.
Wie würdest Du das denn in den DBMS umsetzen, die Du verwendest? Evtl. kann ich das dann ableiten?

Ich vermute, dann muss man wohl in den Apfel beißen und eine unwirksame Aggregatfunktion nehmen, eben max(id) oder so was.
Und dynamisch wird es ja leider mit der PIVOT Anweisung eh nicht.
Sind es also nur 3 Shops, kann man ggF. auch gleich den alten Case When Workaround nehmen.

Aber apropos Dynamic: Es gibt für Oracle/Postgres Verfahren, die die Dynamic auf einem Umweg über XML Verarbeitung erreichen. Sowas kann evtl. auch MSSQL. Also Rohdaten > XML > XML pivot > Konvertierung zurück als Table.

Ja, das geht tatsächlich auch mit MSSQL, wird dort über STUFF mit FOR XML geregelt.
Wird etwas hässlich, aber ich denke, dass ich da keine Wahl habe.

Danke für Deine Meinung.

[defo]

Vielleicht geht es ja, deshalb frage ich ja.
Wie würdest Du das denn in den DBMS umsetzen, die Du verwendest? Evtl. kann ich das dann ableiten?

Ich dachte, MSSQL hat da "nachgeholt" und PIVOT ist für mich nicht zwingend mit Aggregate verbunden, eher gar nicht. Was ich auf die Schnelle gesehen habe entspricht Deiner Feststellung, es geht nicht.
In Postgres ist es eine Funktion namens Crosstab (tablefunction). (Die ist auch nicht dynamisch, also muss das Statement notfalls dynamisch gebaut werden) Die Funktion wirst Du wahrscheinlich kaum nachstellen können/wollen, zumindest nicht für Dein Beispiel. Obwohl TSQL ja auch mächtig ist.
Crosstab ist klasse, es baut auch klaglos über 1000 Spalten, hab die Limits nie nachgeschlagen oder erreicht. Das Statement zu solchen riesigen Ergebnissen ist natürlich generiert.

[Arne Drews]

Hi,

Danke Dir.

Ich habe zumindest den statischen Teil doch noch zusammen bekommen. War eigentlich relativ easy, bin nur gestern nicht mehr drauf gekommen
[highlight=sql]
select pivoted.Artikelnummer, produkt.Bezeichnung, Shop1, Shop2, Shop3
from (
select Artikelnummer, Shop1, Shop2, Shop3
from (
select p.Artikelnummer, s.WebshopName, Exist = iif(p.Artikelnummer is not null, 1, 0)
from tmpPIVOT_Produkte p
full join tmpPIVOT_Webshops s on s.WebshopId = p.WebshopId
) tbl

PIVOT (
max(Exist)
for WebshopName in ( Shop1, Shop2, Shop3 )
) piv
) pivoted

join tmpPIVOT_Produkte produkt on produkt.Artikelnummer = pivoted.Artikelnummer
[/highlight]
Etwas kompliziert aus meiner Sicht vom Aufbau her, aber im Prinzip doch wie gesagt relativ einfach.

Das dynamische scheint wohl auch zu funktionieren, das muss ich allerdings erst nochmal genauer ansehen, weil die Query dann über DynamicSQL aufgebaut werden muss.
Und ich habe bisher nicht getestet, ob ich das in VIEWS verwenden kann. Falls nicht, bleibt mir der Umweg über eine FUNCTION.

Mal schauen, der erste Step hat aber soweit schon mal funktioniert.

EDIT: DynamicSQL in VIEW ist nicht erlaubt, wie schon erwartet. Also FUNCTION...

Gruß Arne

[defo]

Ich habe zumindest den statischen Teil doch noch zusammen bekommen. War eigentlich relativ easy, ..

Das dynamische scheint wohl ..
Und ich habe bisher nicht getestet, ob ich das in VIEWS verwenden kann. Falls nicht, bleibt mir der Umweg über eine FUNCTION.

EDIT: DynamicSQL in VIEW ist nicht erlaubt, wie schon erwartet. Also FUNCTION...

Ok, Teil 1/ "statisch" ist Nutzung von Pivot MIT Aggregat, obwohl man es nicht bräuchte, wenn die Syntax unterstützt würde. Soweit so gut.
Was das dynamische angeht:
Funktions will man ja vielleicht nicht unbedingt, wegen Indizierung, .. ist bei der kleinen -zu erwartenden- Menge an Shops, also fachlich, aber auch keine Drama.
Wie oft werden neue Shops angelegt? Vielleicht kann man sich die Funktion sparen und adhoc den View neu bauen? Also trigger basiert, wenn ein neuer Shop angelegt wird oder gelöscht wird (was kaum vorkommen dürfte).
Bin mir nicht so sicher, wie "sensibel" SQLServer bei neuen Views ist, meine doch schon. Aber es ist ja auch kein struktureller Unterschied, der sich mit der Erneuerung des Views ergibt, also ist vielleicht alles entspannt und es gibt keine Problem mit neu Kompilieren und abhängigen Objekten....

[Arne Drews]

Du hast natürlich recht, die View neu aufzubauen. Das geht auch dynamisch!
Danke für den Tipp, werde ich gleich morgen mal umsetzen.