Hi,

Is it possible to use more group_concat in a single select query in MySql?

__________________
With,
J. Jeyaseelan
Everything Possible

Yeah.. Its possible

Hi,

You can use any number of group_concat function in a select query but the result value depends of the system variable
group_concat_max_len. The default values for this is 1024 (1 kb). If you want to change the the length of that system
variable use can use the below given.

To view the current the length of the group_concat function.
mysql> SHOW VARIABLES LIKE 'group%';
Variable_name Value
-------------------- ------
group_concat_max_len 1024

to change the length of the group_concat function use,
mysql> SET @@group_concat_max_len = 524288; -- 0.5 MB

Now check the length of the variable,
mysql>SHOW VARIABLES LIKE 'group%';
Variable_name Value
-------------------- ------
group_concat_max_len 524288

The value can be set higher, although the maximum effective length of the return value is constrained
by the value of max_allowed_packet. The default value for varialbe max_allowed_packet is 1 mb. You can change value
of this variable also.

To change the value of the max_allowed_packet size, first need to modify this value in the config (my.cnf or .ini) file as given below..

# Set max allowed packet size to 2 MB
max_allowed_packet=2M

Restart the mysql service and the system variable as given below.

mysql>SHOW VARIABLES LIKE 'max_allowed_packet';
mysql>SET @@ max_allowed_packet = 2097152; -- 2 mb, value in bytes..

Now the max_allowed_packet value changed to 2 mb.

__________________
Keep smiling...

Hi,

While using group_concat in my query,the result set cannot be viewed properly.

Iam using TOAD for MYSQL editor for my MYSQL Database.

It displays System.Byte[], this is due to problem in the TOAD for MYSQL or need to change values for any server side variables?

__________________
-Murali..

Hi Murali,
Generaly group_concat functions returns the result in blob format. It seems like like toad error / may not a error also. Anyway, if you want to view the result set in toad using group_concat function means use CAST function and convert the group concat result set into binary.

Eg..
SELECT CAST(group_concat(emp_id) AS BINARY) FROM emp_master;

__________________
Keep smiling...

Again an useful one ....

__________________
-Murali..

Hi,

Please hel to me, GROUP_CONCAT function is available in MS SQL SERVER and ORACLE?

-R.Gopi

Hi,
Group_concat is avilable in Mysql server only,but oracle has equalient group concat functionality that achived in simple query.it is given as below


CREATE OR REPLACE TYPE ntt_varchar2 AS TABLEOF VARCHAR2(4000);
select deptno, cast(collect(ename) as ntt_varchar2) as vals from emp group by deptno

Hi Prasad,

As you said there is no pre definied function (like GROUP_CONCAT in mysql) available in MS-SQL or Oracle.

for MS-SQL we need to use stored function / precedure to get the result as desired. There is no direct function available for group_concat in oracle also.

CREATE OR REPLACE TYPE ntt_varchar2 AS TABLEOF VARCHAR2(4000);
select deptno, cast(collect(ename) as ntt_varchar2) as vals from emp group by deptno

I tried your points in oracle, but it throws error like this...
ORA-00904: "COLLECT": invalid identifier

Can u please clarify ?

__________________
Keep smiling...

Hi Priyan,

May be running the query in a lower version.
COLLECT Operator is available in ORACLE 10g Version.

Check Your Version

SELECT * FROM V$VERSION;

Try the below in ORACLE-10g

SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

CREATE OR REPLACE TYPE Cnvt_Type IS TABLE OF VARCHAR2(100);

CREATE TABLE TEST_VALUE(ID NUMBER,NAME VARCHAR(100));

INSERT INTO TEST_VALUE VALUES(1,'TEST');
INSERT INTO TEST_VALUE VALUES(2,'TEST1');
INSERT INTO TEST_VALUE VALUES(3,'TEST2');
INSERT INTO TEST_VALUE VALUES(4,'TEST3');

SELECT CAST(COLLECT(NAME) AS Cnvt_Type)AS Records FROM TEST_VALUE;

Records
--------
TEST
TEST1
TEST2
TEST3

Prasath,

By using Collection Method as per your idea, it will return the result in different rows but using the GROUP_CONCAT() function in MYSQL will return the record set in a single row with comma seperator.

__________________
-Murali..

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..