Dusan Kolesar wrote:
>
> On Mon, 31 Oct 2005 15:14:15 +0100, Zabach, Elke <[EMAIL PROTECTED]>
> wrote:
>
> > Dusan Kolesar wrote:
> >
> >>
> >> Hello list,
> >> Is it posible to do in SAP DB something like:
> >> SELECT CONCAT (column1 || ', ') FROM t1 GROUP BY column2
> >>
> >> Manual says:
> >> The GROUP clause allows the functions SUM, AVG, MAX/MIN, COUNT,
STDDEV,
> >> and VARIANCE
> >> to be applied not only to entire result tables but also to groups
of
> >> rows
> >> within a result table.
> >>
> >> Or is there any other way how to obtain such a result?
> >> Thank you for your support.
> >>
> >
> > If a GROUP BY - clause is given, every column has to be part of this
> > clause OR part of a aggregate function like SUM/MIN/MAX/COUNT/...
> >
> > --> your select is not possible. And, by the way, what should the
> > semantic be if all rows with the same column2-value are put together
and
> > one of the column1-values is used to produce the output for the
> > corresponding resultrow? Which column1-value ????
> >
> > Which problem do you want to solve?
> >
> > Elke
> > SAP Labs Berlin
> >
> >
> >> Dusan
> >>
>
> Hello, Elke
> let me describe you my situation.
> I have tables:
> //
> CREATE TABLE "STATION"
> (
> "ID" Integer NOT NULL DEFAULT SERIAL,
> "NAME" Varchar(30) ASCII NOT NULL DEFAULT '',
> PRIMARY KEY ("ID")
> )
> //
> CREATE TABLE LINE
> (
> "ID" Integer NOT NULL DEFAULT SERIAL,
> "NAME" Varchar(10) ASCII NOT NULL DEFAULT '',
> PRIMARY KEY ("ID")
> )
> //
> CREATE TABLE STATION_LINE_REL
> (
> "LINE_ID" Integer NOT NULL,
> "STATION_ID" Integer NOT NULL,
> "STATIONORDER" Integer NOT NULL DEFAULT 1
> CONSTRAINT "STATIONORDER" > 0,
> FOREIGN KEY "LINE_ID" ("LINE_ID")
> REFERENCES "LINE" ("ID") ON DELETE CASCADE,
> FOREIGN KEY "STATION_ID" ("STATION_ID")
> REFERENCES "STATION" ("ID") ON DELETE CASCADE
> )
> //
> INSERT STATION (ID, NAME) VALUES (1, 'ST1')
> //
> INSERT STATION (ID, NAME) VALUES (2, 'ST2')
> //
> INSERT STATION (ID, NAME) VALUES (3, 'ST3')
> //
> INSERT STATION (ID, NAME) VALUES (4, 'ST4')
> //
> INSERT LINE (ID, NAME) VALUES (1, 'L1')
> //
> INSERT LINE (ID, NAME) VALUES (2, 'L2')
> //
> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES (1,
1,
> 1)
> //
> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES (1,
2,
> 2)
> //
> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES (1,
3,
> 3)
> //
> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES (2,
2,
> 1)
> //
> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES (2,
4,
> 2)
> //
>
> So between station and line is N:N relation.
> Line L1 : ST1, ST2, ST3
> Line L2 : ST2, ST4
>
> I need make a list of station for 1 station only 1 row
> with lines description column (LINES) = all lines on which station
lies.
> ID NAME NUMLINES LINES
> 1 ST1 1 L1
> 2 ST2 2 L1, L2
> 3 ST3 1 L1
> 4 ST4 1 L2
>
If you always can assure that not more than 2 lines will stop at the
same station, some kind of (outer) join may help.
I did not think of that case, but wanted to assure a correct result for
as many lines per station as are in reality.
The resulting query looks a 'little bit' different:
DECLARE C CURSOR FOR
WITH RECURSIVE PX (ID, NUMLINES, LINES, LAST_LINE) AS
(SELECT STATION_ID, 1, NAME, LINE.ID
FROM STATION_LINE_REL, LINE
WHERE STATION_LINE_REL.LINE_ID = LINE.ID
AND (STATION_ID,LINE_ID) = ANY
(SELECT STATION_ID, MIN(LINE_ID) FROM STATION_LINE_REL
GROUP BY STATION_ID)
UNION ALL
SELECT PX.ID, NUMLINES+1, LINES||', '|| NAME,
STATION_LINE_REL.LINE_ID
FROM STATION_LINE_REL, LINE, PX
WHERE STATION_LINE_REL.LINE_ID = LINE.ID
AND PX.ID = STATION_LINE_REL.STATION_ID
AND PX.LAST_LINE < STATION_LINE_REL.LINE_ID
)
SELECT PX.ID, STATION.NAME, FIXED(NUMLINES, 2), LINES
FROM PX, STATION
WHERE (PX.ID, NUMLINES) = ANY
(SELECT STATION_ID, COUNT(*)
FROM STATION_LINE_REL
GROUP BY STATION_ID)
AND PX.ID = STATION.ID
ORDER BY PX.ID
But it works (tested with an additional line 3 stopping at station 2)
Good luck
Elke
SAP Labs Berlin
> SELECT STATION.ID, MIN (STATION.NAME)AS NAME,
> COUNT (STATION_LINE_REL.LINE_ID) AS NUMLINES
> /* CONCAT (LINE.NAME || ', ') AS LINES */
> FROM STATION
> LEFT JOIN STATION_LINE_REL ON STATION_LINE_REL.STATION_ID =
> STATION.ID
> GROUP BY STATION.ID
>
> And from other side lines list with station description colunm.
> Is it posible to find some elegant solution for this?
>
> Thank you, Dusan
>
> --
> Dusan Kolesar
> Helsinska 19
> 040 13 Kosice
> Slovakia
> e-mail : [EMAIL PROTECTED]
> ICQ# : 160507424
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
