2005/10/31, Dusan Kolesar <[EMAIL PROTECTED]>:
> 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
>
>
> 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?
There is an ugly solution that works only of your path length is fixed.
# pseudo SQL
select line, concat(st1, st2)
from lines, (
select line, stationid as st1, null as st2
from lines
where stationorder = 1
union all
select line, null as st1, stationid as st2
from lines
where stationorder = 2
) ll
where lines.line = ll.line
group by lines.line
order by lines.line
Other that that I don't think this is possible. Aggretation (GROUP
BY) is intended for numeric calculations, hence only SUM, AVG, MAX,
MIN etc are supported.
Kind regards
robert
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
