| CONCAT & SQL (to display rows into columns) [message #252134] |
Tue, 17 July 2007 23:01  |
kumarvk
Messages: 214
Registered: July 2004
|
Senior Member |
|
|
Hi Experts,
My concat function is like this
CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(4000);
hold VARCHAR2(4000);
TYPE cur IS REF CURSOR;
v_cur cur;
BEGIN
OPEN v_cur FOR q;
LOOP
FETCH v_cur INTO hold;
EXIT WHEN v_cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;
my sql is:
SELECT co_eng_name,
rowconcat('SELECT lic_no_cur FROM mps_v_dup_cust WHERE co_eng_name='||co_eng_name) AS duplicust
FROM mps_v_dup_cust gROUP BY co_eng_name;
my table/view is like this
CO_ENG_NAME CO_CUSTID LIC_NO_CUR
ANTHONY SYE CONSTRUCTION PTE LTD 199905399N YM5368S
ANTHONY SYE CONSTRUCTION PTE LTD 199905399N YM5430U
ANTHONY SYE CONSTRUCTION PTE LTD YK3039R
ANTHONY SYE CONSTRUCTION PTE LTD YK5641M
ANTHONY SYE CONSTRUCTION PTE LTD YK6150G
ANTHONY SYE CONSTRUCTION PTE LTD YL655B
ANTHONY SYE CONSTRUCTION PTE LTD YK9255T
ANTHONY SYE CONSTRUCTION PTE LTD YK9498J
ASIAGROUP LEASING PTE LTD A00734/1989N GBA2806A
ASIAGROUP LEASING PTE LTD A00734/1989N XB6100K
ASIAGROUP LEASING PTE LTD A00734/1989N GY9237K
ASIAGROUP LEASING PTE LTD A00734/1989N GY2531G
ASIAGROUP LEASING PTE LTD A00734/1989N GX9027E
ASIAGROUP LEASING PTE LTD A00734/1989N GX5062D
ASIAGROUP LEASING PTE LTD A00734/1989N GW5836T
ASIAGROUP LEASING PTE LTD A00734/1989N GW7920U
I want to display the LIC_NO_CUR rows into columns, I am getting error message if I run the sql, can anyone help me.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
