Sunday, October 2, 2011
Oracle SQL Multiple Rows Return One Row
//Data table DRIVERHP :-
STAFNO | CONTACTNO
---------------------------------
STAF1 11122
STAF1 44455
STAF2 33344
STAF3 66677
STAF3 88888
STAF3 99988
//works on 10g
//the Select Statement 1 :-
SELECT F.STAFNO, RTRIM(XMLAGG(XMLELEMENT(J, F.CONTACTNO,', ')).EXTRACT('//text()'),', ') AS CONTACTNOS
FROM DRIVERHP F
GROUP BY F.STAFNO
//select statement 2 :-
SELECT F.STAFNO, WM_CONCAT(F.CONTACTNO) AS CONTACTNOS
FROM DRIVERHP F
GROUP BY F.STAFNO
//SQL output :-
STAFNO | CONTACTNOS
---------------------------------------------------------------------
STAF1 11122, 44455
STAF2 33344
STAF3 66677, 88888, 99988
Labels:
SQL Codes
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment