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?
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]
