Bharath Nadipally
New User
Joined: 24 Jun 2008
Posts: 22
Location: Hyderabad
|
|
|
|
Hi,
Below are the records in a table.
Dept name
------ -------
10 n1
10 n2
10 n3
20 n4
20 n5
required o/p:
10 n1,n2,n3
20 n4,n5
Is it possible to get required output using one sql query? |
|
kregen
New User
Joined: 16 Mar 2006
Posts: 21
|
|
|
|
hi Bharath Nadipally,
Tabledefinition
| Code: |
DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP
(DEPT SMALLINT NOT NULL
,NAME CHAR(5) NOT NULL
)
;
|
Testdata
| Code: |
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');
INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');
INSERT INTO SESSION.DEPTTMP VALUES(20, 'N1');
INSERT INTO SESSION.DEPTTMP VALUES(20, 'N2');
INSERT INTO SESSION.DEPTTMP VALUES(20, 'N3');
INSERT INTO SESSION.DEPTTMP VALUES(30, 'N4');
INSERT INTO SESSION.DEPTTMP VALUES(30, 'N5');
INSERT INTO SESSION.DEPTTMP VALUES(30, 'N6');
|
And now the SQL-Statment
| Code: |
WITH TEMP1 (DEPT, W#, NAME, ALL_NAME) AS
(SELECT DEPT
,SMALLINT(1)
,MIN(NAME)
,VARCHAR(MIN(STRIP(NAME,B,' ')), 4096)
FROM SESSION.DEPTTMP A
GROUP BY DEPT
UNION ALL
SELECT A.DEPT
,SMALLINT(B.W#+1)
,A.NAME
,B.ALL_NAME || ',' || STRIP(A.NAME,B,' ')
FROM SESSION.DEPTTMP A
,TEMP1 B
WHERE A.DEPT = B.DEPT
AND A.NAME > B.NAME
AND A.NAME = (SELECT MIN(C.NAME)
FROM SESSION.DEPTTMP C
WHERE C.DEPT = B.DEPT
AND C.NAME > B.NAME)
)
SELECT DEPT
,ALL_NAME
FROM TEMP1 D
WHERE W# = (SELECT MAX(W#)
FROM TEMP1 E
WHERE D.DEPT = E.DEPT)
ORDER BY DEPT;
|
a little bit longer... but one statment
Warning: this statment is very slow! don't use it when you have a big table with many rows
cu
kregen |
|
