verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

xp4mer1

Benutzer
Beiträge
12
Hallo Zusammen,

ich möchte eine Abfrage erstellen die mir bestimmte Datensätze einer Tabelle (Adressen) zusammenfasst.

Datensatz Beispiel:

Thomas, Schuller, Römerweg 14, 6700
Ludwig, Schuller, Römerweg 14, 6700
Axel, Müller, Bundweg 1, 6800
Ralf, Schuster, Mintstr. 4, 6850

Gewünschtes Abfrage Ergebnis:

Thomas + Ludwig Schuller, Römerweg 14, 6700
Axel Müller, Bundweg 1, 6800
Ralf Schuster, Mintstr. 4, 6850

Die Logik ist also:
Wenn Nachname und Strasse und PLZ gleich sind, dann soll die Abfrage als Ergebnis die Namen zusammenfassen.

Version: SQL Server 2005

Ich bin für jede Anregung dankbar!
 
Werbung:

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1

wenn Du mit einer Funktion zufrieden bist kannst Du das hier mal ausprobieren.

Code:
CREATE FUNCTION fn_myFunction ()
RETURNS @temp TABLE (
 vorname varchar(50)
, nachname varchar(50)
, Strasse varchar(50)
, plz varchar(50)
)
AS
BEGIN
 DECLARE @IDX int
 SELECT @IDX = count(*) FROM DS_Bsp
 
 DECLARE @vorname varchar(50)
 DECLARE @nachname varchar(50)
 DECLARE @Strasse varchar(50)
 DECLARE @plz varchar(50)
 
 DECLARE @COUNTER int
 SET @COUNTER = 1
 
 INSERT INTO @temp (vorname, nachname,strasse, plz)
 SELECT distinct '' as vorname, nachname, strasse, plz FROM DS_Bsp
 
 WHILE @COUNTER <= @IDX 
 BEGIN
 
  SELECT 
   @vorname=vorname
  , @nachname=nachname
  , @strasse=strasse
  , @plz=plz
  FROM DS_Bsp AS B
  WHERE IDX = @Counter
 
  UPDATE @temp
  SET vorname = @vorname + ' + ' + vorname
  where nachname = @nachname
  and strasse = @strasse
  and plz = @plz
 
  SET @COUNTER = @COUNTER + 1
 
 END
 
 UPDATE @temp
 set vorname = LEFT(vorname, LEN(vorname)-2)
 
 RETURN
END
GO

Lässt sich mit

Code:
select * from fn_myfunction()

abfragen.

[EDIT]
Und wenn du den Nachnamen auch noch in der gleichen Spalte haben willst einfach:

Code:
select vorname + ' ' + nachname, strasse, plz from fn_myfunction()


Gruß Charly
 

xp4mer1

Benutzer
Beiträge
12
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo Charly

besten Dank für Deine prompte Lösung!

Ich habe die Function wie folgt für mein System abgeändert was auch mit Command(s) completed successfully bestätigt wird.

Code:
Alter FUNCTION fn_MGE_Function1 ()
RETURNS @temp TABLE (
 vorname varchar(50)
, nachname varchar(50)
, Strasse varchar(50)
, plz varchar(50)
)
AS
BEGIN
 DECLARE @IDX int
 SELECT @IDX = count(*) FROM OCRD T0
 
 DECLARE @vorname varchar(50)
 DECLARE @nachname varchar(50)
 DECLARE @Strasse varchar(50)
 DECLARE @plz varchar(50)
 
 DECLARE @COUNTER int
 SET @COUNTER = 1
 
 INSERT INTO @temp (vorname, nachname,strasse, plz)
 SELECT distinct T0.CardName as vorname, T0.CardFname as nachname, T0.Address as strasse, T0.Zipcode as plz FROM OCRD T0

where 
((t0.u_b1c_umlfj > '0' OR
t0.u_b1c_umvj > '0') AND
t0.Cardcode != '2000000' AND
t0.CardType = 'C' OR
t0.CreateDate > '20100101') AND
T0.City IS NOT NULL AND
T0.Country = 'AT' AND
T0.Cardname IS NOT NULL AND
T0.Cardname !='' AND
T0.CardFname IS NOT NULL
 
 WHILE @COUNTER <= @IDX 
 BEGIN
 
  SELECT 
   @vorname=CardName
  , @nachname=CardFname
  , @strasse=Address
  , @plz=Zipcode
  FROM OCRD AS B
  WHERE @IDX = @Counter
 
  UPDATE @temp
  SET vorname = @vorname + ' + ' + vorname
  where nachname = @nachname
  and strasse = @strasse
  and plz = @plz
 
  SET @COUNTER = @COUNTER + 1
 
 END
 
 UPDATE @temp
 set vorname = LEFT(vorname, LEN(vorname)-2)
 
 RETURN
END
GO

Beim Abruf mit:

Code:
select * from fn_MGE_function1()

kommt die Fehlermeldung:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

Bin mit meinen Kenntnissen noch nicht in der Lage dies aufzulösen.

Besten Dank für Support
Markus
 

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1,

Du hast warscheinlich NULL-Werte oder Strings kürzer 2 Zeichen in der Spalte vorname stehen.

Das solltest du mit

Code:
IF vorname IS NOT NULL OR LEN(vorname) > 2
BEGIN
   UPDATE @temp
   set vorname = LEFT(vorname, LEN(vorname)-2)
END

abfangen.

Gruß Chary
 

xp4mer1

Benutzer
Beiträge
12
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo Charly

Die Abfrage der Funktion liefert jetzt folgendes Ergebnis laut Screenshot.

Bis auf den Ersten Eintrag sind alle weiteren Vornamen leer.

Hast du eine Erklärung für mich?

mfg,
markus
 

Anhänge

  • script.jpg
    script.jpg
    53 KB · Aufrufe: 2

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1,


dein Fehler liegt in der WHERE-Klausel im SELECT inerhalb der WHILE-Schleife. Das Script läuft zwar x-mal durch die Schleife, bekommt aber nur einmal @IDX=@COUNTER = Wahr.

SELECT
@vorname=CardName
, @nachname=CardFname
, @strasse=Address
, @plz=Zipcode
FROM OCRD AS B
WHERE @IDX = @Counter

Die While-Schleife läuft so lange wie @IDX<=@COUNTER
@IDX wird am Anfang festgelegt und soll die Anzahl der zu durchlaufenden Datensätze festlegen. Ist nicht sehr sauber sollte ja auch nur ein Beispiel sein. Wenn Du Datensätze aus der Tabelle löschst funktioniert die Abfrage nur noch bedingt, da der höchste PK Wert größer als die Anzahl der Zeilen ist.

IDX ist der PK den ich mir einfach mal in deine Beispiel-Tabelle gedacht habe...:eek:

Wenn du mir den PK inkl. Datentyp deiner Tabelle nennst, baue ich dir das Script so um das es als Zähler den PK benutzt.

Gruß Charly
 

xp4mer1

Benutzer
Beiträge
12
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo Charly,

danke für die Info.

Mein PK ist:

Cardcode, nvarchar(15),not null

Gruß
Markus
 

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1,

die WHERE-Klausel macht das ganze etwas unübersichtlich aber im Grunde ist es ziemlich simpel.
Wenn Du nicht die erwarteten Ergebnise bekommst versuch es erstmal ohne den WHERE Teil in den SELECTs.

WHERE cardcode > @COUNTER (ohne den Vergleich gibst eine Endlosschleife)

und

WHERE Cardcode = @Counter (Sorgt dafür das nur ein Wert an jede Variable übergeben wird)

müssen aber stehen bleiben.
Code:
Alter FUNCTION fn_MGE_Function1 ()
RETURNS @temp TABLE
(
 vorname varchar(50)
, nachname varchar(50)
, Strasse varchar(50)
, plz varchar(50)
)
AS
BEGIN
 
DECLARE @vorname varchar(50)
DECLARE @nachname varchar(50)
DECLARE @Strasse varchar(50)
DECLARE @plz varchar(50)
 
DECLARE @COUNTER nvarchar(15)
 
 INSERT INTO @temp (vorname, nachname,strasse, plz)
 SELECT DISTINCT 
  T0.CardName as vorname
 , T0.CardFname as nachname
 , T0.Address as strasse
 , T0.Zipcode as plz 
 FROM OCRD T0
 WHERE
 ((t0.u_b1c_umlfj > '0' OR
 t0.u_b1c_umvj > '0') AND
 t0.Cardcode != '2000000' AND
 t0.CardType = 'C' OR
 t0.CreateDate > '20100101') AND
 T0.City IS NOT NULL AND
 T0.Country = 'AT' AND
 T0.Cardname IS NOT NULL AND
 T0.Cardname !='' AND
 T0.CardFname IS NOT NULL
 
 SELECT @COUNTER = min(cardcode) 
 FROM OCRD T0
 WHERE
 ((t0.u_b1c_umlfj > '0' OR
 t0.u_b1c_umvj > '0') AND
 t0.Cardcode != '2000000' AND
 t0.CardType = 'C' OR
 t0.CreateDate > '20100101') AND
 T0.City IS NOT NULL AND
 T0.Country = 'AT' AND
 T0.Cardname IS NOT NULL AND
 T0.Cardname !='' AND
 T0.CardFname IS NOT NULL
 
 WHILE @COUNTER IS NOT NULL 
 BEGIN
 
 SELECT 
   @vorname=CardName
 , @nachname=CardFname
 , @strasse=Address
 , @plz=Zipcode
 FROM OCRD AS B
 WHERE Cardcode = @Counter
 
 UPDATE @temp
 SET vorname = @vorname + ' + ' + vorname
 WHERE nachname = @nachname
 AND strasse = @strasse
 AND plz = @plz
 
 SELECT @COUNTER = min(cardcode) 
 FROM OCRD T0
 WHERE
 ((t0.u_b1c_umlfj > '0' OR
 t0.u_b1c_umvj > '0') AND
 t0.Cardcode != '2000000' AND
 t0.CardType = 'C' OR
 t0.CreateDate > '20100101') AND
 T0.City IS NOT NULL AND
 T0.Country = 'AT' AND
 T0.Cardname IS NOT NULL AND
 T0.Cardname !='' AND
 T0.CardFname IS NOT NULL
 AND cardcode > @COUNTER
END
 
--   UPDATE @temp
--   SET vorname = LEFT(vorname, LEN(vorname)-2)
 RETURN
END
GO

Als Zähler (@COUNTER) benutze ich jetzt cardcode.

Ich habe die Funktion ohne Testlauf (habe ja keine Daten zum testen) geschrieben. Es kann also sein das sich irgendwo noch ein Fehler eingeschlichen hat. Syntaktisch müsste alles passen.

Gruß Charly
 

xp4mer1

Benutzer
Beiträge
12
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hi Charly

mit den WHEREs komme ich klar.

Ich denke dass sich in dem Update Code noch ein kleiner Fehler versteckt.

Wenn es nur einen eindeutigen Eintrag in der Tabelle gibt, dann soll nur ein Vorname im Ergebnis sein.

Beispiel: Lukas, Wehinger, Abtshalde....

Wenn es zwei Einträge gibt mit gleichen Kriterien wie in der WHERE Clausel, dann soll das Ergebnis Vorname1 + Vorname2 sein und der zweite Eintrag nicht mehr im Ergebnis vorhanden sein.

Beispiel: David + Sarah, Unterkofler, Abtshalde...


Code:
David + Sarah + David           Unterkofler    Abt Franz Pfannerweg 13    6845
David + Sarah + Sarah           Unterkofler    Abt Franz Pfannerweg 13    6845
Lukas + Lukas                        Wehinger      Abtshaldde 14    6832
Katharina + Katharina             Weißkopf      Abtshalde 17    6832
Markus + Stefanie + Markus    Heinzle        Abtshalde 23    6832
Markus + Stefanie + Stefanie   Heinzle       Abtshalde 23    6832
Christian + Christian                Waldner     Abtshalde 32    6832

Besten Dank für Deine Hilfe.
lg, markus
 

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1,

warum stehen da eigentlich noch doppelte drin?

Probier mal für das Update in der While-Schleife

Code:
...
 UPDATE @temp
  SET vorname = 
   CASE 
    WHEN CHARINDEX(@vorname,vorname) = 0 THEN @vorname + ' + ' + vorname
    ELSE vorname
   END
  where ...

Das müsste die doppelten Einträge eliminieren.

Gruß Charly
 

xp4mer1

Benutzer
Beiträge
12
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hi Charly,

das bringt mich jetzt schon viel weiter, fehlt nur noch eine Kleinigkeit (siehe Screenshot)

Die Adressen für die die WHERE Klausel zutrifft sind noch doppelt.

Kannst mir das noch ausbügeln?

lg, markus
 

Anhänge

  • script.jpg
    script.jpg
    32,1 KB · Aufrufe: 2

Charly

Datenbank-Guru
Beiträge
306
AW: verschiedene Datensätze der gleichen Tabelle in einer Abfrage kombinieren

Hallo xp4mer1,

wie soll ich sagen...

Hätte ich mir deine Abfrage mal genauer angesehen.

Wenn Du in der ersten INSERT-Anweisung

Code:
T0.CardName as vorname
durch
Code:
'' as vorname
ersetzt, brauchst Du auch den CASE-Teil im UPDATE nicht mehr.

Hier dürfen noch keine Vornamen eingetragen werden.
Die leere Zeichenkette wird nur eingefügt damit es keine NULL-Werte im Vornamen gibt.

Code:
...
INSERT INTO @temp (vorname, nachname,strasse, plz)
SELECT DISTINCT 
  [COLOR=red][B]''[/B][/COLOR] as vorname
, T0.CardFname as nachname
, T0.Address as strasse
, T0.Zipcode as plz 
FROM OCRD T0
...

Habe mich schon gewundert wo die doppelten Vornamen herkommen.

Jetzt weis ich es.:D

Gruß Charly
 

xp4mer1

Benutzer
Beiträge
12
Hallo Charly,

ich habe noch ein weiteres Feld "Umsatz Vorjahr" in die Abfrage eingebaut.

Wie kann ich bewirken dass "Doppelte Einträge" (beide Familienmitglieder haben einen Umsatz) zusammengezählt und als ein einzelner Eintrag angezeigt wird?

Gruß Markus
script.jpg
 
Werbung:

Charly

Datenbank-Guru
Beiträge
306
Hallo Markus,

wenn Du den Umsatz addieren willst, benutze den Teil der Prozedur in dem Du auch die Namen verbindest.

Code:
...
RETURNS @temp TABLE
(
vorname varchar(50)
, nachname varchar(50)
, Strasse varchar(50)
, plz varchar(50)
, umvj <Datentyp> -- Neue Spalte
)
AS
BEGIN

DECLARE @vorname varchar(50)
DECLARE @nachname varchar(50)
DECLARE @Strasse varchar(50)
DECLARE @plz varchar(50)
DECLARE @umvj <Datentyp> -- Neue Variable
DECLARE @COUNTER nvarchar(15)
...
...
WHILE @COUNTER IS NOT NULL
BEGIN

SELECT
  @vorname=CardName
, @umvj = CardUmsatz -- oder so ähnlich
, @nachname=CardFname
, @strasse=Address
, @plz=Zipcode
FROM OCRD AS B
WHERE Cardcode = @Counter

UPDATE @temp
SET vorname = @vorname + ' + ' + vorname
, umvj = umvj + @umvj
WHERE nachname = @nachname
AND strasse = @strasse
AND plz = @plz

SELECT @COUNTER = min(cardcode)
FROM OCRD T0
WHERE
...

Und darauf achten das es keine NULL in umvj gibt.

Gruß Charly
 
Oben