Yes Murali, i tried that in lower version. ....

But there is someother way to get the result, this is somewhat similar to MySQL group_concat. It's seems to a big query.. just try this..

-- Create table for manipulation....

CREATE TABLE EMP (ID NUMBER, ENAME VARCHAR2(30), DEPT_ID INTEGER);
INSERT INTO EMP VALUES(1,'Raj', 10);
INSERT INTO EMP VALUES(2,'Kumar', 20);
INSERT INTO EMP VALUES(3,'Selvam', 10);
INSERT INTO EMP VALUES(4,'John', 10);
INSERT INTO EMP VALUES(5,'Smith', 10);

-- Query...

WITH DATA
AS
(
SELECT dept_id, ename, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY ename) rnum,
COUNT(*) OVER (PARTITION BY dept_id) count FROM emp
)
SELECT dept_id, ltrim(sys_connect_by_path(ename,','),',') Result_String
FROM DATA WHERE rnum = count
start with rnum = 1
CONNECT BY PRIOR dept_id = dept_id and prior rnum = rnum-1
ORDER BY dept_id;


--------------------------------------------------------------------------------
DEPT_ID RESULT_STRING
--------------------------------------------------------------------------------
10 John,Raj,Selvam,Smith

20 Kumar
--------------------------------------------------------------------------------

However, this is not the same result as we get in mysql using the below mysql query.

SELECT group_concat(ename) as Result_String FROM EMP;

Result_String
---------------------------------------
John,Raj,Selvam,Smith,Kumar

__________________
Keep smiling...

Hi Priyan,

Yes its not similar to MYSQL GROUP_CONCAT().

Not a simple one using SELECT Query but the other way using a PL/SQL Block we can get the same result as per the MYSQL GROUP_CONCAT().

__________________
-Murali..

Hi techies,

There is a new issue here about MySQL GROUP_CONCAT() function.

when i try to use the group_concat() function's result value with IN() Condition, i am not getting expected result set. Please find the ex given below.

eg.
SELECT dept_id, dept_name FROM dept_id
WHERE dept_id IN(SELECT GROUP_CONCAT(emp_dept_id) FROM emp_table)

This query is working but in the where condition, say for example the subquery returns (10,20,30,50) means it takes only the first value (10).

If any one come across this issue please reply the solution...

__________________
Keep smiling...

Hi,

As iam new to Database and atpresent working in ORACLE.
But i heard Oracle and Mysql differ with slight syntax only, so i will try this and tell you if i find any solutions to this.

__________________
Selna.

Hi Priyan/Selena,

Try this one,may be this could help u to solve your GROUP_CONCAT() Issue,

__________________
-Murali..

Hi Murali,

Yes, it's working with dynamic query but it will make impact on the query performance. Your SP handles the numerics in the group concat function. It would be more complicated if we handling strings in the group concat function with dynamic query.

There must be some solution for this issue !!!

__________________
Keep smiling...

Hi Priyan,

Have u noticed one thing.Try this

CREATE TABLE testing(id INTEGER,name VARCHAR(10));
INSERT INTO testing VALUES (1,'1000,1010');
SELECT * FROM table_name WHERE table_id IN (SELECT name FROM testing);

The above query returns only one record from the table as per the first value from the field.

This is because the difference as follows

SELECT * FROM table_name WHERE table_id IN ('1000,1010'), trims the string to the INTEGER and takes just the first value.so the query will work as
SELECT * FROM table_name WHERE table_id IN ('1000');

Might be the same thing happens in the GROUP_CONCAT().

As you said that it will be very tedious for handling strings, each values has to be concated as a string and to get the result from the query.

Hope believe there will be more flexible String Functions are expected in MYSQL5.1.

__________________
-Murali..

Hi,

Have u noticed one thing,

But didn't get the expected result?

__________________
-Murali..

Hi murali,

I have tried your sp but have modified it as per my format of variable dec.

Here is the code,

drop procedure if exists sp_test;
create procedure sp_test()
sp_test:
begin
declare var_query text;
declare var_id integer;
SET var_query:= 'SELECT CAST(GROUP_CONCAT(mytable_id)AS BINARY) INTO var_Id FROM mytable';
prepare pre_query FROM var_query;
execute pre_query;
select var_id;
end sp_test;

I got the following Error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'var_query; execute pre_query;select var_id;end sp_test' at line 7

I have checked for my version and its
select version();
5.0.22-max-log

Could u help me what i have missed out here?

__________________
Selna.

Hi,

The problem is u cannot use the declared variable as in prepare statement command.

drop procedure if exists sp_test;
create procedure sp_test()
sp_test:
begin
declare var_query text;
declare var_id integer;
SET var_query:= 'SELECT CAST(GROUP_CONCAT(mytable_id)AS BINARY) INTO var_Id FROM mytable';
SET @var_query := var_query;
prepare pre_query FROM @var_query;
execute pre_query;
select var_id;
end sp_test;

CALL sp_test();

Now again u will get an error Undeclared variable: var_Id;, indicates that the dynamic sql query can save the value in the global/session variable inside the single query, i.e
'SELECT CAST(GROUP_CONCAT(mytable_id)AS BINARY) INTO @var_Id FROM mytable';

Can't be like this
'SELECT CAST(GROUP_CONCAT(mytable_id)AS BINARY) INTO var_Id FROM mytable';

So here is the code,

__________________
-Murali..