dantegabriel
Apr 19, 2015, updated at Apr 20, 2015 (UTC)
view4106
view9
0
Goto Top

MS SQL 2000 Mehrere Zeilen mit unterschiedlichen Werten zu einer zusammenfassen

GermansolvedQuestionDatabasesDevelopment
Nabend zusammen,

ich versuche aktuell eine View zu erstellen, bedauerlicherweise ist die zugrundeliegende Datenbankstruktur alles andere als Hilfreich. (Unabhängig vom Alter der Datenbank). Prinzipiell liegt mein Problem bei einer Tabelle der Datenbank, in der Telefondaten gespeichert werden. Da mir aktuell jedoch die Ideen ausgehen hoffe ich das ihr mir weiterhelfen könnt face-smile

Die Tabelle phone sieht wie folgt aus. (Ein Beispiel)

phone_id owner_id phone
1 1 Telefonnummer 1
2 1 Telefonnummer 2
4 1 Telefonnummer 3
7 3 Telefonnummer 2
8 5 Telefonnummer 1
9 5 Telefonnummer 2

Die Struktur wie Sie sein müsste wäre jedoch in etwa so (wenn davon ausgegangen wird, das es max. 4 Nummern pro owner gibt)


owner_idphone_1 phone_2 phone_3 phone_4
1 Telefonnummer 1 Telefonnummer 2 Telefonnummer 3 -
3 - Telefonnummer 2 - -
8 Telefonnummer 1 Telefonnummer 2 - -

Bisherige Überlegungen:

JOIN mit Subselect
Bei dieser Idee wollte ich die Tabelle auf sich selbst Joinen, jedoch in einer anderen Reihenfolge. Das Problem hierbei ist das ich zwar den letzten und den ersten Wert bekomme, den zweiten, dritten usw. jedoch nicht. (Einschränkung durch TOP)

Prinzipiell ist das bislang meine einzige brauchbare Idee gewesen, Dinge wie row_number, Aneinanderreihungen von Subselects usw. habe ich beim weiteren drüber nachdenken wieder verworfen, weil entweder die Funktion nicht unterstützt wird oder das ganze nicht funktioniert hat.

Ich hoffe ihr habt noch eine hilfreiche Idee, ich glaub ich drehe solange am Rad.. xD naja jedenfalls ärgert es mich das es nicht so funktioniert wie ich mir das vorgestellt habe...

Gruß und schon mal Danke
Dante Gabriel
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 269657

Url: https://administrator.de/contentid/269657

Printed on: April 18, 2024 at 09:04 o'clock

9 Comments
Latest comment
Goto Top
Member: Biber
Biber Apr 20, 2015 at 09:47:11 (UTC)
Goto Top
Moin DanteGabriel,

das wird umständlich...

Ab MS-SQLServer 2005 gibt es für diese Anforderungen PIVOT/UNPIVOT-Features.

Dann könntest du deine Telefonliste in etwa so in die gewünschte Darstellung bringen:
Select * from 
 (
  SELECT owner_id , 'Phone_' || phone_id as phone_x, phone  
  from Telefonliste
) as t
PIVOT 
( Max (phone)
FOR Phone_x in ( [Phone_1], [Phone_2], [Phone_3], [Phone_4])
) as ersterVersuch

ungetestet, sollte aber sinngemäß in die richtige Richtung gehen.

Leiser hat aber der SQLServer 2000 noch nix in Richtung Kreuztabellen implementiert (AFAIK).

Hier müsstest du dann so etwas über eine stored procedure abfackeln.
Eine der handelsüblichen Suchmaschinen liefert einige Implementierungsbeispiele.
Auf mssqltips ist eine kurze Linkliste für Beispiele.

Mit reinem SQL wirst du unter SQLServer 2000 nicht weit kommen, fürchte ich.
Ist das denn ein "eingefrorenes Projekt", für das keine Migration/kein Upgrade zu einer aktuelleren Version möglich ist?

Grüße
Biber
Member: DanteGabriel
DanteGabriel Apr 20, 2015 updated at 11:05:33 (UTC)
Goto Top
Moin Biber,

ja MS SQL 2005 würde die Sache wirklich einfacher machen ... erheblich... naja aber irgendwie muss das mit MS SQL 2000 gehen.

Wobei ich mittlerweile auch immer mehr glaube das es mit reinem SQL nicht funktioniert...

Ein "eingefrorenes Projekt" ist es nicht, viel schlimmer noch es ist aktiv im Einsatz ^.^ ... theoretisch gäbe es auch neue Versionen des Programms für die die Datenbank ist, aber es macht keinen Sinn darin zu investieren, da eine alternative Lösung über kurz oder lang eingeführt werden muss.

Die View benötige ich diesem Konstrukt zur Anbindung an einen CTI Server, dieser kann die x-fachen Zeilen je Owner nicht verarbeiten da die Software einen Ergebnissatz erwartet und nicht (wie ich feststellen musste) bis zu 6 für eine eindeutige Person.

Eine Kreuztabelle für den Abgleich zu verwenden ist denke ich auch eine gute Idee nur wüsste ich nicht wie ich die einzelnen Einträge separieren könnte. Bedauerlicherweise gibt es nicht mal Unterscheidungsmerkmale in der Art der Nummer (Nur ein Beschreibungsfeld was meistens nicht mal befüllt ist) aber so etwas wie Rufnumerntypen (Tel. , Fax, Mobil odä.) gibt es ja nicht... sonst hätte ich das bereits im SELECT mit einer CASE Operation filtern können.

In etwa...
SELECT phone.owner_id, CASE (WHEN phone.phone_type = 1 THEN phone.phone END) as phone_1
...

aber leider gibt es das nicht, da es diese Typen nicht gibt.

Wodurch ich beim schreiben der Prozedur auch stocken würde, da es keinen Indikator gibt den ich so ohne weiteres als Spaltenwert bestimmen könnte.


Hinzu kommt das ich nur selbst versuchen kann die Struktur der Datenbank zu verstehen, Dokumentationen odä. wären zwar nett aber... naja face-smile

Gruß
Dante Gabriel
Member: Logan000
Logan000 Apr 20, 2015 updated at 11:31:18 (UTC)
Goto Top
Moin Moin,

Zitat von @DanteGabriel:
Bedauerlicherweise gibt es nicht mal Unterscheidungsmerkmale in der Art der Nummer
(Nur ein Beschreibungsfeld was meistens nicht mal befüllt ist) aber so etwas wie Rufnumerntypen (Tel. , Fax, Mobil odä.)
gibt es ja nicht... sonst hätte ich das bereits im SELECT mit einer CASE Operation filtern können.
Das ist aus meiner Sicht das größere Problem dabei. Hier soltest du darübernachdenken ob es sich lohnt ein weiteres Feld "anzuflanschen".

ja MS SQL 2005 würde die Sache wirklich einfacher machen ... erheblich... naja aber irgendwie muss das mit MS SQL 2000 gehen.
Wobei ich mittlerweile auch immer mehr glaube das es mit reinem SQL nicht funktioniert...

Versuchs mal mit (ich neine der SQL 2000 kannte rank() schon.):
SELECT pp1.owner_id, pp1.Phone1, pp2.Phone2, pp3.Phone3, pp4.Phone4
FROM
(
	SELECT p1.owner_id, p1.phone as 'Phone1'  
	FROM 
	(SELECT rank() OVER (PARTITION BY a.owner_id ORDER BY a.phone_id) as phonerank, a.owner_id, a.phone_id, a.phone
		FROM Test_phone a 
	) as p1 WHERE p1.phonerank=1
) as pp1
LEFT JOIN
(
	SELECT p2.owner_id, p2.phone as 'Phone2'  
	FROM 
	(SELECT rank() OVER (PARTITION BY a.owner_id ORDER BY a.phone_id) as phonerank, a.owner_id, a.phone_id, a.phone
		FROM Test_phone a 
	) as p2 WHERE p2.phonerank=2
) as pp2
ON (pp1.owner_id=pp2.owner_id)
LEFT JOIN
(
	SELECT p3.owner_id, p3.phone as 'Phone3'  
	FROM 
	(SELECT rank() OVER (PARTITION BY a.owner_id ORDER BY a.phone_id) as phonerank, a.owner_id, a.phone_id, a.phone
		FROM Test_phone a 
	) as p3 WHERE p3.phonerank=3
) as pp3
ON (pp1.owner_id=pp3.owner_id)
LEFT JOIN
(
	SELECT p4.owner_id, p4.phone as 'Phone4'  
	FROM 
	(SELECT rank() OVER (PARTITION BY a.owner_id ORDER BY a.phone_id) as phonerank, a.owner_id, a.phone_id, a.phone
		FROM Test_phone a 
	) as p4 WHERE p4.phonerank=4
) as pp4
ON (pp1.owner_id=pp4.owner_id)

Gruß L.
Member: Biber
Solution Biber Apr 20, 2015 updated at 18:00:47 (UTC)
Goto Top
Moin Logan000,

na, dann geht es sogar mit einem Inline-SELECT und ohne Rank() kürzer:
select owner_id, max(phone_1) as phone_1, max(phone_2) as phone_2
               , max(phone_3) as phone_3, max(phone_4) as phone_4 from (
select t1.owner_id, t1.Id 
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 1 then phone else '' end as phone_1   
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 2 then phone else '' end as phone_2   
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 3 then phone else '' end as phone_3   
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 4 then phone else '' end as phone_4   
from telliste t1     
) x group by owner_id

Sollte mit den obigen Beispieldaten ergeben:
OWNER_ID | PHONE_1         | PHONE_2         | PHONE_3         | PHONE_4
---------+-----------------+-----------------+-----------------+--------
       1 | Telefonnummer 1 | Telefonnummer 2 | Telefonnummer 3 |        
       3 | Telefonnummer 2 |                 |                 |        
       5 | Telefonnummer 1 | Telefonnummer 2 |                 |

Grüße
Biber

P.S. allerdings kann es weder bei Logans RANK() noch bei meinem Pseudo-Ranking eine "Phone_2"-Nummer geben, wenn es keine "Phone_1"-Nummer gibt.
Das ist eine Abweichung von der gewünschten Ausgabe oben.
Member: DanteGabriel
DanteGabriel Apr 20, 2015 updated at 12:22:21 (UTC)
Goto Top
@Biber @Logan000

wenn es diese Funktion in MS-SQL 2000 gäbe face-smile habe es eben mal portiert, aber die Funktion kennt die Datenbank nicht. Soweit ich das gesehen habe gibt es als einzige Möglichkeit zum zählen die Funktion IDENTITY, aber ich hab nicht wirklich verstanden wie ich die in diesem Kontext nutzen kann.

@Logan000

Das Einfügen einer weiteren Spalte wäre keine gute Idee, zum einen weil ich nicht weiß wie die Abfragen des Programms aussehen, zum anderen sind aktuell fast 200k Nummern in der Tabelle ohne das ich weiß welche Nummer FAX und welche Nummer die normale Telefonnummer ist. Zu guter letzt würde das Problem ja fortgeführt da im Programm keine Möglichkeit besteht außer über die Beschreibung die Art der Nummer zu benennen.

Gruß
Dante Gabriel

@edit Biber:
Ich denke der Fall würde nicht eintreffen, da die Zählung ja sowieso bei 1 beginnen müsste, d.h. sobald eine Nummer vorhanden ist müsste es auch einen rank() geben. (Nur dann vielleicht nicht 2,3,4...)
Member: Biber
Solution Biber Apr 20, 2015 updated at 18:00:44 (UTC)
Goto Top
Moin DanteGabriel,

ich habe ja in meiner Variante auf Verwendung einer fertigen RANK()-Funktion verzichtet.

probier doch mal ein abgespecktes
select t1.owner_id, t1.Id 
  . (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id )
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 1 then phone else '' end as phone_1   
  , case when (select count(*)+1 from telliste where owner_id=t1.owner_id and id < t1.id ) = 2 then phone else '' end as phone_2   
from telliste t1   
where owner_id < 100
... auf deiner Telefonliste.

Falls tatsächlich der heutige PK auf der Kombination von OWNER_ID und ID liegt, dann wird dieses Inline-Select auch gar nicht soooo inperformant sein.

Und zu dem P.S.: siehe ganz oben deine Wunsch-Ausgabe für OWNER_ID 3.

Grüße
Biber
Member: DanteGabriel
DanteGabriel Apr 20, 2015 updated at 14:30:36 (UTC)
Goto Top
Ich habe deine (kürzere) Version gerade einmal portiert, aber das funktioniert schon recht gut.

Zwar ist die owner_id mehrfach vorhanden,aber vermutlich löst dies ein GROUP BY (Wenn ich das richtig sehe führst du oben das MAX nur auf damit kein Fehler vom SQL geliefert wird? (Wegen GROUP BY und keine Aggregationsfunktion)


Ich versuche das mal Beispielhaft in einer Tabelle auszudrücken, denke das ist für die Visualisierung hilfreich.

Ausgangssituation mit Fakedaten. In diesem Beispiel beschränke ich mich auf die owner ID's 0, 33, 34 und 36

phone_id owner_id phone
16 0 +49 123456789
17 0 +49 234567891
20 0 +49 345678912
18 0 +49 123456789
21 0 +49 456789123
94912 33 +49 987654321
97191 33 +49 874563219
94913 33 +49 745612389
245706 33 +49 23479856
95655 34 +49 542318723
95656 34 +49 134579523
95654 34 +49 218974562
77 36 +49 4572314545
78 36 +49 1287964256
95880 36 +49 232156789
95881 36 +49 123452315
95733 36 +49 531247893
95734 36 +49 231457893

Das Ergebnis mit der SQL Anweisung sieht dann mehr so aus:

owner_id phone_id (leer) phone_1 phone_2
0 16 1 +49...
0 17 2 +49 ..
33 245706 4
34 95655 2 +49
34 95656 3
34 95654 1 +49
36 77 1 +49...
34 78 2 +49...
... ... ... ... ...

@ps:
Ja ich weiß was du meinst face-smile Aber ich kann die Rufnummern eh nicht zuordnen von d.h. spielt das keine Rolle wenn dann "Rufnummer 2" in Spalte phone_1 auftaucht.

EDIT:
Geil das scheint es zu sein, die Idee an die phone Nummer durch das max zu kommen bin ich gar nicht gekommen genial face-smile

Und du hast Recht, die Abfrage ist vergleichsweise Performant, für die 200k Einträge braucht er "nur" 8sec aber das ist später in der view eher zweitrangig. Dann versuche ich das jetzt mal in dem JOIN zur verwursteln der in der view die Tabelle phone anspricht.

Gruß
Dante Gabriel
Member: Biber
Solution Biber Apr 20, 2015 updated at 18:00:38 (UTC)
Goto Top
Moin Dante Gabriel,

ich bin auch relativ zuversichtlich, dass die Strategie wie in meinem Kommentar von 14.00h zum Ziel führt.

Sicherlich auch, wenn du dem o.a. Statement noch ein "CREATE VIEW TelListePivot as (..)" voranstellst.

Falls es also klappt, setz' bitte den Beitrag auf "Gelöst".
Falls es nur in Ansätzen klappt, dann poste deine berichtigte, getestete und funktionierende Variante.

Dann haben auch andere mit einem vergleichbaren Problem etwas davon.

Grüße
Biber
Member: DanteGabriel
DanteGabriel Apr 20, 2015 at 18:00:28 (UTC)
Goto Top
Ja das hat wirklich sehr gut funktioniert, der Clou ist dabei echt das max(phone_X) damit das GROUP BY nicht auf die Nase fällt.

An dieser Stelle mega Dank für deine Idee, die hat echt gefehlt face-smile

Also zur Lösung:

In der Form wie du (Bieber) es beschrieben hast funktioniert es ohne Probleme, das wichtige hierbei ist dann der Bezug zur owner_id. In einer VIEW oder allg in einem Join muss der Ergebnissatz dann so aussehen wie er in dem Beitrag von Biber oder meinem vorherigen Beitrag zu sehen ist. Nur um es an dieser Stelle nochmal auszudrücken ist dies das, was dass Subselect als Ergebnissatz liefern muss, damit bei einem JOIN die Daten in der gewünschten Form angefügt werden können.

Nehmen wir für die Lösung eine weitere Tabelle, genannt person mit den typischen Spalten und Einträgen so sähe das JOIN von der Tabelle person zur Tabelle phone so aus:

SELECT person.person_id, person.firstname as 'Vorname', person.lastname as 'Nachname'', phone_1 as 'Rufnummer 1', phone_2 as 'Rufnummer 2', phone_3 as 'Rufnummer 3', phone_4 as 'Rufnummer 4'  
FROM tbl.person as person
JOIN (
	select owner_id, max(phone_1) as phone_1, max(phone_2) as phone_2
               , max(phone_3) as phone_3, max(phone_4) as phone_4 from (
	select t1.owner_id, t1.phone_id 
  , case when (select count(*)+1 from tbl.phone where owner_id=t1.owner_id and phone_id < t1.phone_id ) = 1 then phone else '' end as phone_1   
  , case when (select count(*)+1 from tbl.phone where owner_id=t1.owner_id and phone_id < t1.phone_id ) = 2 then phone else '' end as phone_2   
  , case when (select count(*)+1 from tbl.phone where owner_id=t1.owner_id and phone_id < t1.phone_id ) = 3 then phone else '' end as phone_3   
  , case when (select count(*)+1 from tbl.phone where owner_id=t1.owner_id and phone_id < t1.phone_id ) = 4 then phone else '' end as phone_4   
	from tbl.phone t1     
	) x group by owner_id
) as tele on tele.owner_id = person.person_id

Im übrigen ist dies wie es scheint eine hervorragende Möglichkeit beliebig viele Zeilen in Spalten "umzuwandeln", insofern keine besseren Funktionen dafür bereit stehen, in jedem Fall ist es denke ich die beste Lösung für die SQL Generation 2000 face-smile

Gruß
Dante Gabriel
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
AlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 CommentsDaniEnd of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment