MSSQL - Transpose

Re(6): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 21:34:16 GMT

Also die Lieferanten sind natürlich in anderen Tabellen angelegt, ich wollte nur das Beispiel nicht unnötig verkomplizieren - in der View die schlussendlich entstehen soll (eigentlich sinds mehrere leicht unterschiedliche) sind ca. 30 Spalten vorhanden

Eine Linenum o.ä. hätte ich jedoch nicht gesehen, sehe jedoch auch den Zweck dahinter nicht. Irgendwo gibts glaub ich noch die Möglichkeit einen Stammlieferanten/Bevorzugten Lieferanten zu definieren, das wars dann aber schon.

Danke dir jedenfalls

Re(7): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:53:40 GMT

ok... wenn die Daten selten geändert werden, dann ist die Möglichkeit mit der sp und der weiteren Tabelle ok.

Re(6): MSSQL - Transpose

zeddicus — Sun, 23 Jan 2011 19:52:19 GMT

kommt immer drauf an wie viel "müll" entsteht, und wie groß der aufwand ist ihn zu vermeiden. wenn es so ist wie er schreibt, daß die quelltable auch so gut wie nie geändert wird würd ich mir auch um die performance keine sorgen machen. aber es gibt ansonsten immer möglichkeiten mit flugzeugträgern möwen zu töten. (ja mir ist durchaus bewußt daß die performance nicht die beste ist, allerdings liegt sie noch VOR der von manchen view konstellationen, da nur auf trigger updgedatet wird, und zu diesem zeitpunkt wie beim view auch schon ein execution plan besteht)

Re(5): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:43:53 GMT

Das Datenmodell an sich ist ja eh schön.

finde ich persönlich nicht, denn ich gehe zwar davon aus, dass die Tabelle Lieferantusw. mehr Spalten enthält aber bei deiner Angabe fehlt mir die 1. eine eindeutige ID der jeweiligen Records und 2. würde ich den Datenbestand eben so aufbauen, dass wenn die Artikelnummer mehrfach vorkommen kann eben soetwas wie eine LineNum oder eben ID bezogen auf den jeweiligen Artikel ebenso abgelegt wird.

außerdem müssten mMn die Hersteller sowieso in einer ganz eigenen Tabelle hinterlegt sein und du schreibst in die Lieferantusw. Tabelle nur die ID des Lieferanten und nicht dessen Namen.

Re(5): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:43:53 GMT

Das Datenmodell an sich ist ja eh schön.

Re(5): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:43:53 GMT

Das Datenmodell an sich ist ja eh schön.

Re(5): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:43:53 GMT

Re(3): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 19:40:29 GMT

das mit den left joins stimmt schon, aber eigentlich mache ich da nichts anderes wie eine PIVOT Funktion (nur händisch). Rein theoretisch müsste ein PIVOT auf die 1. View funktionieren.

Re(2): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 19:18:02 GMT

Hm .. ich habs ausprobiert und muss sagen - die Lösung sieht auch interessant aus.

Jetzt gilts wohl abzuwägen was mehr "Zeit" kostet. Eine "relativ" komplexe View (LEFT OUTER JOINs sind ja nicht sooo nett) oder eine Zwischentabelle.

Ich lass es mir mal durch den Kopf gehen, danke

Re(4): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 19:06:15 GMT

Das Datenmodell an sich ist ja eh schön.
Zu einem Artikel gibts mehrere Lieferanten und für jeden Lieferanten natürlich eine Bestellnummer (die ja von Lieferant zu Lieferant variieren kann).

Das was ich erzeugen muss ist nicht so schön, da setzt jedoch das verarbeitende Programm die Grenzen.

Re(3): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 14:56:05 GMT

aber warum Datenmüll erzeugen, wenns eine View mindestens bzw. sogar eleganter löst?

Desweiteren: wenn das wirklich eine Produktivdatanbank ist die noch 5 Jahre weiterverwendet wird... warum ist bspw. die Lieferant-Tabelle so mies "designed"?

Re(5): MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 14:53:23 GMT

und außerdem wird durch die neue tabelle nur unnötiger daten"müll" erzeugt. Eine Tabelle befüllen mit Daten die ja sowieso vorhanden sind finde ich... naja... nicht sooo elegant.

Re(4): MSSQL - Transpose

kiff-kiff — Sun, 23 Jan 2011 11:51:06 GMT

wenn es nicht erforderlich ist -> Wurscht

ich seh das ähnlich - vor allem möchte ich nach zwei Monaten auf einen Blick (performant!!) erkennen, was ich da gecoded hab ...

Re(3): MSSQL - Transpose

King_Uli — Sun, 23 Jan 2011 10:50:30 GMT

zum Punkt Performanz: Ich kannmir nicht vorstellen dass die "Zeddicus" Lösung wirklich performant ist - wenn es nicht erforderlich ist -> Wurscht;

Ich kann mich nur erinnern dass solche Konstrukte eher langsam sind.

Re(2): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 08:21:27 GMT

Danke, das Ranking muss ich mir mal genauer anschauen (für die Zukunft). Ich habe mittlerweile die Lösung von zeddicus implementiert und bin damit eigentlich recht zufrieden

Danke dir jedenfalls für die Ideen.

Re(4): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 08:20:13 GMT

http://forum.geizhals.at/t737877,6317177.html#6317177

Re(2): MSSQL - Transpose

Somnatic — Sun, 23 Jan 2011 08:20:04 GMT

Hehe, MSSQL kennt zwar ein TOP, aber kein LIMIT ... das heisst nix mit limit 0,1

Jetzt gäbe es natürlich die Lösung das mit zwei verschachtelten Schleifen abzubilden, ist aber sicher alles andere als performant.

Ich habe mir jetzt mal angesehen wie mit den Daten umgegangen wird und bin zum Schluss gekommen dass an den Daten nur ganz selten (2,3 mal am Tag) was geändert wird, diese aber oft abgefragt werden.

Ich habe daher die Lösung von zeddicus genommen und meinen Anforderungen nach adaptiert. Nun wird nach einer Änderung der Daten mittels eines Triggers eine entsprechende Tabelle mit den gesamten Daten aktualisiert. Die Views greifen dann auf diese Zwischentabelle zurück. Das hat den Vorteil dass ich die Views sehr einfach halten kann, weil alle Daten im Prinzip schon in der Zwischentabelle drin sind und ich nur noch nach den entsprechenden Eigenschaften filtern muss.
Nachteil ist natürlich dass sich die Daten 2x in der Datenbank befinden, das wird jedoch dadurch gelöst dass diese Tabelle eben quasi "von der DB selbst" generiert wird. Der Client bekommt davon gar nichts mit.

Ich denke das ist eine Lösung mit der wir sicher die nächsten 5 Jahre überdauern können (und wahrscheinlich auch länger).

Daher danke an alle die hier mitgeholfen haben

Re: MSSQL - Transpose

kiff-kiff — Sun, 23 Jan 2011 07:44:54 GMT

kann dein SQL subquery? dann würde ich es mal so versuchen:


SELECT ARTIKEL,NAME, 
(SELECT ARTIKEL from ARTIKELLIEFERANT where ARTIKEL = a.ARTIKEL limit 0,1),
(SELECT ARTIKEL from ARTIKELLIEFERANT where ARTIKEL = a.ARTIKEL limit 1,1),
(SELECT ARTIKEL from ARTIKELLIEFERANT where ARTIKEL = a.ARTIKEL limit 2,1) from ARTIKEL a

du kannst die spalten dann noch mit AS überschreiben lassen und auch sonst daran tunen.

Grundsätzlich funktionierts (bei MySQL), ob es in einer Umgebung läuft stellst du selber fest

@die anderen Lösungsvorschläge: keep it simple!!

Re: MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 00:02:20 GMT

direkt mit PIVOT wird das nicht hinhauen.

ich würde das ganze über einen zwischenschritt mittels einer view realisieren die dir so etwas wie eine Zeilen ID bezogen auf die Artikelnummer generiert. Das geht am einfachsten über RANK() OVER.

Konkret für dein Beispiel:

1. Schritt die View die dir das "ranking" erstellt:

SELECT     t0.artikel, t0.name, t1.lieferantname, t1.lieferantbestnr, rank() OVER (Partition BY T0.artikel ORDER BY t1.lieferantbestnr) AS liefid
FROM         dbo.[@artikel] AS t0 LEFT OUTER JOIN
                      dbo.[@artikellieferant] AS t1 ON t0.artikel = t1.artikel

ich habe diese als dbo.v_liefid abgespeichert. Das SQL Server Management Studio wird dir beim Ausführen direkt in der View einen Fehler ausgeben - funktioniert aber trotzdem

Nun haben wir pro Artikel eine LieferantenID mit der wir weiterarbeiten können.

2. Schritt wäre nun deine gewünschte View damit das oben gewünschte Ergebnis ausgeben wird:

select t0.artikel, t0.name, t1.lieferantname, t1.lieferantbestnr, t2.lieferantname, t2.lieferantbestnr, t3.lieferantname, t3.lieferantbestnr
from dbo.[@artikel] as t0
left join dbo.v_liefid t1 on t0.artikel = t1.artikel and t1.liefid = 1
left join dbo.v_liefid t2 on t0.artikel = t2.artikel and t2.liefid = 2
left join dbo.v_liefid t3 on t0.artikel = t3.artikel and t3.liefid = 3

die Spalten musst halt dann noch mit AS so benennen wie du es gerne hättest. Diese Kosmetikaufgabe überlasse ich dir.

Re: MSSQL - Transpose

blitzfreak — Sun, 23 Jan 2011 00:02:20 GMT

mit PIVOT wird das nicht hinhauen.

ich würde das ganze über einen zwischenschritt mittels einer view realisieren die dir so etwas wie eine Zeilen ID bezogen auf die Artikelnummer generiert. Das geht am einfachsten über RANK() OVER.

Konkret für dein Beispiel:

1. Schritt die View die dir das "ranking" erstellt:

SELECT     t0.artikel, t0.name, t1.lieferantname, t1.lieferantbestnr, rank() OVER (Partition BY T0.artikel ORDER BY t1.lieferantbestnr) AS liefid
FROM         dbo.[@artikel] AS t0 LEFT OUTER JOIN
                      dbo.[@artikellieferant] AS t1 ON t0.artikel = t1.artikel

select t0.artikel, t0.name, t1.lieferantname, t1.lieferantbestnr, t2.lieferantname, t2.lieferantbestnr, t3.lieferantname, t3.lieferantbestnr
from dbo.[@artikel] as t0
left join dbo.v_liefid t1 on t0.artikel = t1.artikel and t1.liefid = 1
left join dbo.v_liefid t2 on t0.artikel = t2.artikel and t2.liefid = 2
left join dbo.v_liefid t3 on t0.artikel = t3.artikel and t3.liefid = 3

die Spalten musst halt dann noch mit AS so benennen wie du es gerne hättest. Diese Kosmetikaufgabe überlasse ich dir.

Re(3): MSSQL - Transpose

King_Uli — Sat, 22 Jan 2011 17:16:35 GMT

Das war ja auch nur ein Beispiel um die mehreren Positionen abzufragen.

Den Rest musst noch selber machen sprich einfach eine zweite Schleife die dir die entsprechenden Werte in die Felder gibt.

Re(4): MSSQL - Transpose

Somnatic — Fri, 21 Jan 2011 21:54:52 GMT

Wow, danke vielmals ... ich glaube so könnte das funktionieren. Ich werde das sofort mal testen

Re(2): MSSQL - Transpose

Somnatic — Fri, 21 Jan 2011 21:54:27 GMT

Hmm .. also wenn ich mir das so ansehe baut mir das eine CSV auf, macht aber keine "Spalten". Ich brauche die Daten jedoch zwingend in Spalten angeordnet

Re: MSSQL - Transpose

King_Uli — Fri, 21 Jan 2011 16:05:56 GMT

Hallo !

meine Lösung dafür wäre es mittels min() und max() den niedriegsten bzw den höchsten Wert herauszuzuschen.

Du wirst nicht umhin kommen eine etwas wildere Schleifkonstruktion zu basteln.

Ich poste hier meinen Code für eine Funktion , den du gerne abwandeln kannst.
( es handelt sich hier um ein Beispiel wo es um Autoren zu einem Buch (identifiziert über die ISBN) geht)

-> die @ISBN wäre in deinem Fall die Artikel Nummer

CREATE FUNCTION Bib.udf_Autoren_pro_ISBN
(@ISBN NVARCHAR(13))
RETURNS nvarchar(256)
AS
BEGIN
  DECLARE @Autoren nvarchar(256)
  DECLARE @NeuerAutor nvarchar(64)
  DECLARE @laufvariable int = 0
  DECLARE @MinAutorID int
  DECLARE @MaxAutorID int

  -- ermittle min und max Autor ID für den gegebenen Buch Titel
  SELECT @MinAutorID = MIN(AutorID) FROM tbl_ISBNAutor WHERE ISBN = @ISBN
  SELECT @MaxAutorID = MAX(AutorID) FROM tbl_ISBNAutor WHERE ISBN = @ISBN

  -- initialisiere Variablen für die WHILE Schleife
  SET @laufvariable = @MinAutorID
  SET @Autoren = ''

  WHILE @laufvariable <= @MaxAutorID
    BEGIN

      SET @NeuerAutor = ''

      SELECT @NeuerAutor = LTRIM(ISNULL(a.Vorname,'') + ' ' + a.Nachname)
      FROM Bib.tbl_ISBNAutor AS i
      JOIN Bib.tbl_BuchAutor AS a ON i.AutorID = a.AutorID
      WHERE i.ISBN = @ISBN AND i.AutorID = @laufvariable

      SET @Autoren = @Autoren + @NeuerAutor

      SET @laufvariable = @laufvariable + 1

      -- Komma setzen wenn nicht der letzte Autor
      IF @laufvariable <= @MaxAutorID AND @NeuerAutor <> ''
        SET @Autoren = @Autoren + ', '
    END
  RETURN @Autoren
END

Re(3): MSSQL - Transpose

zeddicus — Fri, 21 Jan 2011 11:08:05 GMT

ich auch net, drum sind meine lösungen meist eher improvisiert

also die tabelle neu aufbaun (is nur so dahingeschrieben, muss net funktionieren, und ist sicher keine schöne lösung) (ok, eigentlich superhäßlich)

wenn die tabelle angelegt ist und neuetabelle heißt, gings in etwa so:

truncate table neuetabelle

insert into neuetabelle (artikel, [name]) select distinct artikel, [name] from artikel
update neuetabelle
set lief1 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel),
set liefnr1 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief1),
set lief2 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1),
set liefnr2 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief2),
set lief3 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1 and not lieferantname = neuetabelle.lief2),
set liefnr3 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief3)

danach hättest die table. ist aber wirklich sauhäßlich, aber zumindest hättest mal deine tabelle

Edit: für die top 1 queries müsstest ev. noch ein order by einbaun, damits deterministischer ist welche 3 ausgewählt werden (zb. order by lieferantname)

Bin mir jetzt net sicher ob der inhalt gleich aktualisiert wird zwecks der subqueries, oder ob dus in einzelne updates aufteilen müsstest

Re(3): MSSQL - Transpose

zeddicus — Fri, 21 Jan 2011 11:08:05 GMT

ich auch net, drum sind meine lösungen meist eher improvisiert

also die tabelle neu aufbaun (is nur so dahingeschrieben, muss net funktionieren, und ist sicher keine schöne lösung) (ok, eigentlich superhäßlich)

wenn die tabelle angelegt ist und neuetabelle heißt, gings in etwa so:

truncate table neuetabelle

insert into neuetabelle (artikel, [name]) select distinct artikel, [name] from artikel
update neuetabelle
set lief1 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel),
set liefnr1 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief1)

update neuetabelle
set lief2 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1),
set liefnr2 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief2)

update neuetabelle
set lief3 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1 and not lieferantname = neuetabelle.lief3),
set liefnr3 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief3)

danach hättest die table. ist aber wirklich sauhäßlich, aber zumindest hättest mal deine tabelle

Edit: für die top 1 queries müsstest ev. noch ein order by einbaun, damits deterministischer ist welche 3 ausgewählt werden (zb. order by lieferantname)

Re(3): MSSQL - Transpose

zeddicus — Fri, 21 Jan 2011 11:08:05 GMT

ich auch net, drum sind meine lösungen meist eher improvisiert

also die tabelle neu aufbaun (is nur so dahingeschrieben, muss net funktionieren, und ist sicher keine schöne lösung) (ok, eigentlich superhäßlich)

wenn die tabelle angelegt ist und neuetabelle heißt, gings in etwa so:

truncate table neuetabelle

insert into neuetabelle (artikel, [name]) select distinct artikel, [name] from artikel
update neuetabelle
set lief1 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel),
set liefnr1 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief1)

update neuetabelle
set lief2 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1),
set liefnr2 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief2)

update neuetabelle
set lief3 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1 and not lieferantname = neuetabelle.lief3),
set liefnr3 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief3)

danach hättest die table. ist aber wirklich sauhäßlich, aber zumindest hättest mal deine tabelle

Re(3): MSSQL - Transpose

zeddicus — Fri, 21 Jan 2011 11:08:05 GMT

ich auch net, drum sind meine lösungen meist eher improvisiert

also die tabelle neu aufbaun (is nur so dahingeschrieben, muss net funktionieren, und ist sicher keine schöne lösung) (ok, eigentlich superhäßlich)

wenn die tabelle angelegt ist und neuetabelle heißt, gings in etwa so:

truncate table neuetabelle

insert into neuetabelle (artikel, [name]) select distinct artikel, [name] from artikel
update neuetabelle
set lief1 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel),
set liefnr1 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief1),
set lief2 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1),
set liefnr2 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief2),
set lief3 = (select top 1 lieferantname from artikellieferant where artikel  = neuetabelle.artikel and not lieferantname = neuetabelle.lief1 and not lieferantname = neuetabelle.lief3),
set liefnr3 = (select lieferantbestnr from artikellieferant where artikel = neuetabelle.artikel and lieferantname = neuetabelle.lief3)

danach hättest die table. ist aber wirklich sauhäßlich, aber zumindest hättest mal deine tabelle

Edit: für die top 1 queries müsstest ev. noch ein order by einbaun, damits deterministischer ist welche 3 ausgewählt werden (zb. order by lieferantname)

Bin mir jetzt net sicher ob der inhalt gleich aktualisiert wird zwecks der subqueries, oder ob dus in einzelne updates aufteilen müsstest

Re(2): MSSQL - Transpose

Somnatic — Fri, 21 Jan 2011 09:33:19 GMT

Hmm .... Ja das klingt nach einer möglichen Lösung. Ich muss allerdings zugeben dass ichs auch auf manuellem Weg schon nicht geschafft habe dieses "Layout" zu erzeugen - bin aber auch nicht so DER SQL Spezialist

Re: MSSQL - Transpose

zeddicus — Fri, 21 Jan 2011 09:24:14 GMT

Hm. Also direkt als View bin ich grad auch überfragt. Wärs ne alternative die gewünschte Table durch eine SP erzeugen und per Trigger updaten zu lassen?

Re: MSSQL - Transpose

kiff-kiff — Fri, 21 Jan 2011 06:36:47 GMT

würde mich auch interessieren.

kenn zwar deine app nicht, ich selbst löse das in zwei schritten:

select product
while product
select lieferant

jetzt nur abstrakt, kein code

MSSQL - Transpose

Somnatic — Thu, 20 Jan 2011 20:36:54 GMT

Hallo

Ich melde mich mal wieder mit einem Problem an dem ich derzeit scheitere. Ich habe folgende Datenstruktur:


Table: ARTIKEL
ARTIKEL     NAME
100-1001    Produkt1
100-1002    Produkt2

Table: ARTIKELLIEFERANT
ARTIKEL      LIEFERANTNAME       LIEFERANTBESTNR
100-1001     DiTech              0815
100-1001     E-Tec               4711
100-1001     Amazon              1112
100-1002     Teufel              A012
100-1002     Hofer               8273

Was ich nun gerne hätte ist folgendes:


ARTIKEL   NAME        LIEF1   LIEFNR1    LIEF2  LIEFNR2    LIEF3  LIEFNR3
100-1001  Produkt1    DiTech  0815       E-Tec  4711       Amazon 1112
100-1002  Produkt2    Teufel  A012       Hofer  8273       NULL   NULL

Dabei kommen maximal 3 Zeilen zu einem "Artikel" vor, sollten es mehr sein -> irrelevant.

Irgendwie komme ich bei der Fragestellung nicht ganz auf einen grünen Zweig. Versucht hätte ich es mittels PIVOT aber entweder es geht damit nicht oder ich habe mit zu dumm angestellt.

Achja, ich würde das ganze für viele Artikel in einer View benötigen, d.h. ich weiss nicht ob eine Lösung evtl. mittels Cursor hier sinnvoll wäre?

SQL Server wäre ein 2005er wenn das relevant ist

Danke mal im Voraus an die Spezialisten hier