Elke Schmidtke wrote:
> Hi all,
> I'm looking for a function similar to sum for char data fields.
> situation:
> table faecher (pnr int, fach char(20))
> content of faecher:
> 1, 'Mathe'
> 1, 'Bio'
> 2, 'Deutsch'
> 2, 'Geo'
> 2, 'Sport'
>
> For some reports we generate from our tables we need a representation
> of fach for equal pnr in one string in the following manner:
> 1 => Mathe Bio
> 2 => Deutsch Geo Sport
>
> For numerical values of the table I could just use
> SELECT pnr, sum(fach)
> FROM faecher
> GROUP BY fach
>
> But how can it be done for char.
> For this example I have simplified the table, we use keys instead of
> fach as char(20).
>
> Any Help ?
> Thanks Elke
Hi Elke (says Elke),
I would like to point out, that
SUM(numcol), where numcol has the values 3 and 7 and 12 in its 3 records,
would not result in 3 7 12, but in 22, the arithmetic sum of it.
Therefore a sum of a non-numeric-column is not possible in that sense.
What you are looking for is some recursive concatenation of the different
school (I assume) classes.
If the maximum number of different classes would be known, an outer join could be
possible.
But for an unknown number, use this:
DECLARE MY_CURSOR CURSOR FOR
WITH RECURSIVE PX (PNRGRUPPE, ALLE_FAECHER) AS
(
SELECT
PNR, MIN (FACH)
FROM
FAECHER
GROUP BY
PNR
UNION ALL
SELECT
PNRGRUPPE, ALLE_FAECHER || ' ' || FACH
FROM
FAECHER, PX
WHERE
PNRGRUPPE = PNR
AND
INDEX (ALLE_FAECHER, FACH) = 0
)
SELECT
PNRGRUPPE, MAX(ALLE_FAECHER)
FROM
PX
GROUP BY
PNRGRUPPE
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
