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