Zeilen in Spalten: Subselect klappt nicht (Unterabfrage für eine Zeile liefert meh...

[HalliGalli]

Hallo,

ich bin neu hier im Forum und hoffe ihr könnt mir bei folgendem Problem lösen:

• Tabelle "Kunden" und "Datenschutz" sollen vereint werden, Ergebnis soll Kunde mit den verschiedenen Datenschutzmerkmalen sein
• es gibt eine eindeutige Referenz, über die beide Tabellen verbunden werden können
• Zugriff auf die Tabellen mittels SQL-Developer (v.3.2.20.09)
• vorhandene Kenntnisse: rudimentär (habe bisher für alle Anforderungen die Abfragen zusammen-geklöppelt bekommen, aber hier beiße ich mir seit Tagen die Zähne aus)
• woran ich scheitere: ich bekomme die Datenschutzkennzeichen nicht in die Spalten sortiert (s. Beispieltabellen)

Tabelle Kunden:

KDNR	KREF	MARKE	DATUM
800100	12		11.12.10
800100	12	A	31.05.10
800101	13	A	01.01.10
800101	13	B	02.01.10

Tabelle Datenschutz:

KREF	MARKE	MERKMAL	ZUSTIMMUNG
12		EMAIL	Y
12		TEL	N
12		POST	N
12	A	EMAIL_A	N
12	A	TEL_A	Y
12	A	POST_A	N
13	A	EMAIL_A	Y
13	A	TEL_A	Y
13	A	POST_A	Y
13	B	EMAIL_B	N
13	B	TEL_B	N
13	B	POST_B	N
...
...

Gewünschtes Ergebnis:

KDNR	MARKE	DATUM	EMAIL	TELEFON	POST
800100		11.12.10	Y	N	N
800100	A	31.05.10	N	Y	N
800101	A	01.01.10	Y	Y	Y
800101	B	02.01.10	N	N	N

Was ich bisher versucht habe (bitte nicht lachen):

SELECT DISTINCT LPAD (K.KDNR,6,'0'),
K.MARKE,
K.DATUM,

 CASE K.MARKE
  WHEN '' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='EMAIL')
  WHEN 'A' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='EMAIL_A')
  WHEN 'B' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='EMAIL_B')
END AS EMAIL,

 CASE K.MARKE
  WHEN '' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='TEL')
  WHEN 'A' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='TEL_A')
  WHEN 'B' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='TEL_B')
END AS TELEFON,

 CASE K.MARKE
  WHEN '' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='POST')
  WHEN 'A' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='POST_A')
  WHEN 'B' THEN (SELECT D.ZUSTIMMUNG FROM DATENSCHUTZ D JOIN KUNDEN K ON K.KREF=D.KREF AND D.MERKMAL='POST_B')
END AS POST

FROM KUNDEN K JOIN DATENSCHUTZ D ON K.KREF = D.KREF

Wenn ich in die Subselects mich auf eine Kundenreferenz beschränke, funktioniert die Abfrage. Wenn ich sie aber starte wie oben angegeben, dann bekomme ich die Rückmeldung
"ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:"

Ungeachtet der Fehlermeldung gibt es sicherlich eine einfachere / elegantere Lösung für mein Problem. Es wäre super, wenn mir jemand bei meinem Problem unter die Arme greifen könnte.

Vielen Dank vorab!

VG,
HalliGalli

[defo2]

In Oracle ließe sich das über eine Pivotabfrage machen:

select *
  from (select column1, column2 from tables where conditions)
PIVOT(aggregate_function(column2)
   for column2 in(expr1, expr2, .. . expr_n) | subquery)
 order by expression [ asc | desc ];
-- https://www.techonthenet.com/oracle/pivot.php

Welchen Zugriff Du dafür benutzt, ist egal.

[Ralf Jansen]

Solltest du nicht in jedem Subselect auch auf Marke filtern? Nur so würde das Ergebnis bei deinen gezeigte Beispieldaten eindeutig.

[HalliGalli]

In Oracle ließe sich das über eine Pivotabfrage machen:

select *
from (select column1, column2 from tables where conditions)
PIVOT(aggregate_function(column2)...

Hallo defo2,

mit dem Pivot komme ich leider gar nicht zurecht. Mir fehlt hier die Vorstellung:
- welche Aggregatfunktion für meinen Fall passend wäre (eigentlich möchte ich ja nichts "verdichten", sondern die Werte von D.ZUSTIMMUNG in die Spalten EMAIL/TEL/POST überführen)
- wie ich die 2 Stufen abgebildet bekomme:
1) MERKMAL (EMAIL + EMAIL_A + EMAIL_B werden zu einer Spalte zusammengefasst, genauso für TEL und POST)
2) die Werte (Y/N) aus ZUSTIMMUNG derselben Zeile werden in den Spalten EMAIL/TEL/POST dargestellt

SELECT DISTINCT LPAD (K.KDNR,6,'0'), K.MARKE, K.DATUM, D.ZUSTIMMUNG
FROM KUNDEN K, DATENSCHUTZ D
PIVOT
(
AGGR? (D.ZUSTIMMUNG)   /*müsste hier nicht dieselbe Spalte wie in FOR steht rein? */
FOR
D.MERKMAL IN ('EMAIL', 'EMAIL_A', 'EMAIL_B')    /*und wie POST bzw. TEL lösen? */
)

Solltest du nicht in jedem Subselect auch auf Marke filtern? Nur so würde das Ergebnis bei deinen gezeigte Beispieldaten eindeutig.

Hallo Ralf,

danke für den Hinweis! Das Ergebnis bleibt leider dasselbe (ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile).
Die Marke sollte auch durch D.MERKMAL='EMAIL' / 'EMAIL_A' / 'EMAIL_B' eindeutig sein (da in Abhängigkeit der Marke ein jeweils eindeutiges Datenschutz-Merkmal enthalten ist).


Vorab schon mal vielen Dank für eure Geduld!

[defo2]

Du musst eine min oder max verwenden. Es ist nicht so offensichtlich, aber wenn die Daten entlang der "X und Y" Achsen eindeutig sind, ergibt sich automatisch keine Aggregation. Min(1) bleibt 1, ebenso bei Max(). Ein Count(1) ist offensichtlich nicht, was man möchte.

[jum]

So klappt es mit PIVOT:

WITH kunden (kdnr,   kref,   marke,   datum) AS
(SELECT 800100,12, NULL, to_date('11.12.10') FROM dual UNION ALL
 SELECT 800100,12,  'A', to_date('31.05.10') FROM dual UNION ALL
 SELECT 800101,13,  'A', to_date('01.01.10') FROM dual UNION ALL
 SELECT 800101,13,  'B', to_date('02.01.10') FROM dual),
  datenschutz(kref, marke, merkmal, zustimmung) AS
(SELECT 12, NULL, 'EMAIL'  , 'Y' FROM dual UNION ALL
 SELECT 12, NULL, 'TEL'    , 'N' FROM dual UNION ALL
 SELECT 12, NULL, 'POST'   , 'N' FROM dual UNION ALL
 SELECT 12, 'A', 'EMAIL_A', 'N' FROM dual UNION ALL
 SELECT 12, 'A', 'TEL_A'  , 'Y' FROM dual UNION ALL
 SELECT 12, 'A', 'POST_A' , 'N' FROM dual UNION ALL
 SELECT 13, 'A', 'EMAIL_A', 'Y' FROM dual UNION ALL
 SELECT 13, 'A', 'TEL_A'  , 'Y' FROM dual UNION ALL
 SELECT 13, 'A', 'POST_A' , 'Y' FROM dual UNION ALL
 SELECT 13, 'B', 'EMAIL_B', 'N' FROM dual UNION ALL
 SELECT 13, 'B', 'TEL_B'  , 'N' FROM dual UNION ALL
 SELECT 13, 'B', 'POST_B' , 'N' FROM dual)
SELECT *
  FROM
(SELECT k.kdnr,  k.marke, k.datum, substr(merkmal,1,3) sm, d.zustimmung
  FROM kunden k
  JOIN datenschutz d
    ON (k.kref = d.kref AND NVL(k.marke,'x')=NVL(d.marke,'x')))
PIVOT (
  MAX(zustimmung)
  FOR sm in ('EMA' as email, 'TEL' as tel, 'POS' as post)
)
ORDER BY kdnr, marke nulls first;

KDNR MARKE DATUM EMAIL TEL POST
800100 _ 11.12.2010 Y N N
800100 A 31.05.2010 N Y N
800101 A 01.01.2010 Y Y Y
800101 B 02.01.2010 N N N

[HalliGalli]

Hallo zusammen,

vielen Dank für die Lösung!

Du musst eine min oder max verwenden. ...

Das hatte erstmal die Klemme gelöst und ich konnte zumindest die grundsätzliche Funktionsweise von PIVOT besser verstehen. Folgendes Schnipsel hatte bereits funktioniert:

SELECT * FROM
(SELECT K.KDNR, K.MARKE, K.DATUM, D.ZUSTIMMUNG, D.MERKMAL
 FROM KUNDEN K
 JOIN DATENSCHUTZ D
 ON K.KREF=D.KREF AND K.MARKE=D.MARKE)
PIVOT(
  MAX(ZUSTIMMUNG)
  FOR MERKMAL
   IN('EMAIL', 'TEL', 'POST')
 );

Die nächste Sackgasse war dann die Zusammenführung unterschiedlicher Marken in die selben Spalten. Auf die Idee, einfach über substr die MERKMALE für die Selektion anzupassen wäre ich glaube ich nicht gekommen

So klappt es mit PIVOT:
...(SELECT k.kdnr, k.marke, k.datum, substr(merkmal,1,3) sm, d.zustimmung [/CODE]

VIELEN DANK EUCH!!