none
SQL Abfrage für die Suche aller Zuordnungen zwischen 2 Spalten einer Tabelle mit unterschiedlichen Zuordnungen RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich habe ein weiteres Problem zu lösen und zwar werden in jeweils 2 Spalten einer Tabelle den Produkten Pn entsprechende Hersteller Hn zugeordnet. Es existieren insgesamt 2 Spaltenbereiche mit jeweils 2 Spalten, wobei die Produkte und die Hersteller in beiden Spaltenbereichen vorkommen können. Die einzelnen Produkte und Hersteller sind beim Start der Abfrage nicht bekannt und auch die Anzahl der Hersteller, denen ein Produkt zugeordnet ist, ist variabel.

    Es sollen nun alle Produkte gesucht werden, die mehr als einem Hersteller zugeordnet sind, absteigend sortiert nach der Anzahl der unterschiedlichen Hersteller. Die Ausgabe ist in der 2. Abb. zu sehen. Zum besseren Überblick sind die infrage kommenden Daten farblich markiert

    Ausgangstabelle:

    Declare @myTab as Table(ProduktPalette1 char(2), Hersteller1 char(3),ProduktPalette2 char(2),Hersteller2 char(3));

    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P1', 'H11', 'P5', 'H51');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P8', 'H81', 'P1', 'H13');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P1', 'H11', 'P6', 'H61');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P3', 'H31', 'P4', 'H42');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P1', 'H12', 'P8', 'H83');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P4', 'H41', 'P1', 'H14');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P1', '', 'P7', '');
    Insert into @myTab(ProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2)
    values('P8', 'H82', 'P5', '');

    Das Ergebnis soll nun wie folgt aussehen:

    Ich hoffe, ich erhalte hier wieder passende Lösungsvorschläge.

    Gruß Jürgen

    Freitag, 19. Oktober 2012 08:47
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen, Du kannst einfach weitere Abfragen über ein LEFT JOIN oder INNER JOIN an die Pivot-Abfrage anhängen.

    with Normalisiert
as
(
Select ProduktPalette1 as Produkt, Hersteller1 as Hersteller
from @myTab
where Hersteller1 <> ''
Union ALL
Select ProduktPalette2, Hersteller2
from @myTab
where ProduktPalette2 <> ''
and Hersteller2 <> ''),
Ranking0 as
(
Select Produkt, Hersteller, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY
count(*) desc, Hersteller) as rrn
from Normalisiert
group by Produkt, Hersteller
),
Ranking as
(Select *
from Ranking0 r1
where exists
        (
        Select *
        from Ranking0 r0
        where r0.Produkt = r1.Produkt
        and r0.rrn > 1
        )
)
Select a.Produkt, a.[1], a.[2], a.[3], a.[4], c.Gesamtanzahl
from
(
SELECT Produkt, [1],[2],[3],[4]
FROM Ranking
PIVOT (min(Hersteller) FOR rrn IN ( [1],[2],[3],[4] )) as x) a
inner join
(Select Produkt, max(rrn) as Anzahl
from Ranking
group by Produkt) as b
on a.Produkt = b.Produkt
inner join
(Select Produkt, count(Hersteller) as Gesamtanzahl
from Ranking
group by Produkt) as c
on a.Produkt = c.Produkt
order by Anzahl desc, Produkt
;

     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Montag, 22. Oktober 2012 09:50
    Montag, 22. Oktober 2012 07:12
    |
    Beantworter

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen,
    hier macht vor allem die nicht normalisierte Struktur einen Teil der Unübersichtlichkeit aus. Daher kommt zuerst eine CTE zur Normalisierung und danach werden in den CTEs die einzelnen Anforderungen abgedeckt. Am Ende kommt ein PIVOT, welches für die Sortierung noch mal mit der letzten CTE gejoined werden muss.

    with Normalisiert
as
(
Select ProduktPalette1 as Produkt, Hersteller1 as Hersteller
from @myTab
where Hersteller1 <> ''
Union ALL
Select ProduktPalette2, Hersteller2
from @myTab
where ProduktPalette2 <> ''
and Hersteller2 <> ''),
Ranking0 as
(
Select Produkt, Hersteller, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY
count(*) desc, Hersteller) as rrn
from Normalisiert
group by Produkt, Hersteller
),
Ranking as
(Select *
from Ranking0 r1
where exists
     (
     Select *
     from Ranking0 r0
     where r0.Produkt = r1.Produkt
     and r0.rrn > 1
     )
)
Select a.Produkt, a.[1], a.[2], a.[3], a.[4]
from
(
SELECT Produkt, [1],[2],[3],[4]
FROM Ranking
PIVOT (min(Hersteller) FOR rrn IN ( [1],[2],[3],[4] )) as x) a
left join
(Select Produkt, max(rrn) as Anzahl
from Ranking
group by Produkt) as b
on a.Produkt = b.Produkt
order by Anzahl desc, Produkt
;

    Falls Du das ganze für mehr als 4 Hersteller brauchst, musst Du das SQL erweitern, oder Dir PIVOT mit dynamischem SQL anschauen.

    Die Spaltenüberschriften kann dann ja immer noch die GUI gerade rücken, oder?
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Freitag, 19. Oktober 2012 12:49
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    Hallo Christoph,

    danke für deine schnelle Antwort und deinen Lösungsvorschlag, deshalb will ich auch schnell antworten, ohne dass ich schon mal getestet habe.

    Ich hatte all diese Problemstellungen bereits mit Excel und VBA gelöst. Allerdings sind wir jetzt auf eine SQL-Datenbank umgestiegen und da bin ich nun mal "noch" nicht so firm drin.

    Die nicht normalisierte Struktur rührt aus der 1zu1 Übernahme der Daten aus einer ExcelTabelle in die MS SQL-Server Datenbank.

    Ja, es können auch mehr als 4 Hersteller sein, aber das dynamische Pivot bekomme ich wohl hin. Die Spaltenüberschriften sind nicht ganz so tragisch. Das sollte ich hinbekommen.

    Jetzt mache ich mich ans Anpassen. Ich gebe Rückmeldung, ob alles geklappt hat.

    Vielen herzlichen Dank

    Gruß Jürgen

    Freitag, 19. Oktober 2012 13:08
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo nochmal,

    Christoph, dein Lösungsvorschlag funktioniert prima, wenn die Anzahl der Hersteller bekannt ist.

    Nun ist aber die Anzahl variabel (dynamisch), was ich leider doch nicht hinbekomme.

    Meine Idee wäre, zunächst eine Normalisierung durchzuführen, um die beiden Spaltenblöcke zu einer neuen HilfsTabelle mit den Spalten "Produkt" und "Hersteller" zusammen zu führen und dabei auch gleichzeitig leere Zuordnungen und auch Duplikate (z.B. P1/H11) zu entfernen:

    Select ProduktPalette1 as Produkt, Hersteller1 as Hersteller
	from @myTab
	where ProduktPalette2 <> '' and Hersteller1 <> ''
Union
Select ProduktPalette2, Hersteller2
	from @myTab
	where ProduktPalette2 <> '' and Hersteller2 <> ''
group by ProduktProduktPalette1, Hersteller1, ProduktPalette2, Hersteller2

    Soweit klappt es bei mir.

    Produkt	Hersteller
P1	H11
P1	H12
P1	H13
P1	H14
P1	H15
P3	H31
P4	H41
P4	H42
P5	H51
P6	H61
P8	H81
P8	H82
P8	H83

    Weiter wäre dann meine Idee, in der neuen Hilfstabelle, die einzelnen Produkte in der Spalte "Produkt" zu zählen und wenn die Anzahl größer als 1 ist, bleibt das Produkt stehen und die dazugehörigen Hersteller werden in den weiteren Spalten daneben aufgelistet. Zum Schluss dann noch die so entstandenen Records nach Anzahl der Hersteller sortieren und fertig!

    Ich hoffe, ich habe keinen Gedankenfehler begangen, aber wenn es so geht, dann fehlen mir die passenden SQL-Kenntnisse.

    Vielleicht kann jedoch jemand helfen!

    Gruß Jürgen

    Freitag, 19. Oktober 2012 16:33
    |
  • Question
    Anmelden
    0
    Anmelden

    oups,

    den Datensatz (P1;H15) muss man sich wegdenken, da ich an der Ausgangstabelle rumgespielt habe und dem Produkt P1 noch einen weiteren Hersteller zugeordnet habe.

    Freitag, 19. Oktober 2012 16:38
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Christoph,

    ich habe es nun doch mit den variablen Spalten hinbekommen :-)

    Ich möchte jetzt noch eine zusätzliche Spalte "Gesamtzahl" mit der Anzahl der gefundenen Hersteller wie u.a. einfügen:

    Produkt	Gesamtzahl	1	2	3	4
P1	NULL	H11	H12	H13	H14
P8	NULL	H81	H82	H83	NULL
P4	NULL	H41	H42	NULL	NULL

    Was muss ich noch in der Abfrage ergänzen?

    Gruß Jürgen

    Samstag, 20. Oktober 2012 12:04
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen, Du kannst einfach weitere Abfragen über ein LEFT JOIN oder INNER JOIN an die Pivot-Abfrage anhängen.

    with Normalisiert
as
(
Select ProduktPalette1 as Produkt, Hersteller1 as Hersteller
from @myTab
where Hersteller1 <> ''
Union ALL
Select ProduktPalette2, Hersteller2
from @myTab
where ProduktPalette2 <> ''
and Hersteller2 <> ''),
Ranking0 as
(
Select Produkt, Hersteller, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY
count(*) desc, Hersteller) as rrn
from Normalisiert
group by Produkt, Hersteller
),
Ranking as
(Select *
from Ranking0 r1
where exists
        (
        Select *
        from Ranking0 r0
        where r0.Produkt = r1.Produkt
        and r0.rrn > 1
        )
)
Select a.Produkt, a.[1], a.[2], a.[3], a.[4], c.Gesamtanzahl
from
(
SELECT Produkt, [1],[2],[3],[4]
FROM Ranking
PIVOT (min(Hersteller) FOR rrn IN ( [1],[2],[3],[4] )) as x) a
inner join
(Select Produkt, max(rrn) as Anzahl
from Ranking
group by Produkt) as b
on a.Produkt = b.Produkt
inner join
(Select Produkt, count(Hersteller) as Gesamtanzahl
from Ranking
group by Produkt) as c
on a.Produkt = c.Produkt
order by Anzahl desc, Produkt
;

     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Montag, 22. Oktober 2012 09:50
    Montag, 22. Oktober 2012 07:12
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    Hallo Christoph,

    alles klar! Damit ist mein Problem gelöst.

    Danke und nen schönen Tag ebenfalls noch.

    Jürgen

    Montag, 22. Oktober 2012 09:49
    |