Comma Separated

[yasmine]

Hi friends,
I need to create a table which refers the fields from an existing table as follows:

Create table a (select content,sid, count(*) count from b group by content order by count desc limit 0,30)

This code works well and creates a table.
But the content field having different sid's for each value.
ex:

content sid
Apple 3
Apple 5
Apple 8
Mango 7
Mango 10.....

Is there any way to store the sid in comma separated values in mysql?

Like as follows:

content sid
Apple 3,5,8
Mango 7,10.....

Please, help me out.........

Thanx n Regards
Yas.......

[chaarmann]

Hi friends,
I need to create a table which refers the fields from an existing table as follows:

Create table a (select content,sid, count(*) count from b group by content order by count desc limit 0,30)

This code works well and creates a table.
But the content field having different sid's for each value.
ex:

content sid
Apple 3
Apple 5
Apple 8
Mango 7
Mango 10.....

Is there any way to store the sid in comma separated values in mysql?

Like as follows:

content sid
Apple 3,5,8
Mango 7,10.....

Please, help me out.........

Thanx n Regards
Yas.......

You must join many records into one, and you can only do that with the group-by function. If you have a small and limited maximum number of each fruit, you can do that with a normal SQL, but it's very tricky and lengthy. better you use a stored procedure for it.

But if you have a small maximum number of each fruit, for example 3, then you can do with normal SQL:

create table fruits as (select content, null as sids from a group by content);
insert into fruits set sids=
concat(
(select sid from a where a.content = sid.content limit 0,1) , ',',
(select sid from a where a.content = sid.content limit 1,1) , ',',
(select sid from a where a.content = sid.content limit 2,1) , ',',
);

I hope you get the idea.

At the end, you can run an SQL to replace multiple occurences of ',,' with an empty string to delete unnecessary commas at the end in 'sids' column.

[mwasif]

Try this query

CREATE TABLE a (SELECT content,GROUP_CONCAT(sid) sid, count(*) count FROM b GROUP BY content ORDER BY count desc LIMIT 0,30)

[chaarmann]

Try this query

CREATE TABLE a (SELECT content,GROUP_CONCAT(sid) sid, count(*) count FROM b GROUP BY content ORDER BY count desc LIMIT 0,30)

group_concat is availabe only since mySql version 4.1 and above.

[ronverdonk]

group_concat is availabe only since mySql version 4.1 and above.

So why don't we ask yasmine what version of MyQL she's got? Yasmine?

Ronald

[yasmine]

So why don't we ask yasmine what version of MyQL she's got? Yasmine?

Ronald

yah....
i'm having the mysql version 4.1.11.

Thanks for the coding

This query works well and creates a table. But the survey id field does not contain comma separated values.
The sid field contains only [BLOB - 1.0 KB] values for all the rows.
I don't know what is it.
Can u help me.........????

Thanx n Regards
Yas........

[yasmine]

You must join many records into one, and you can only do that with the group-by function. If you have a small and limited maximum number of each fruit, you can do that with a normal SQL, but it's very tricky and lengthy. better you use a stored procedure for it.

But if you have a small maximum number of each fruit, for example 3, then you can do with normal SQL:

create table fruits as (select content, null as sids from a group by content);
insert into fruits set sids=
concat(
(select sid from a where a.content = sid.content limit 0,1) , ',',
(select sid from a where a.content = sid.content limit 1,1) , ',',
(select sid from a where a.content = sid.content limit 2,1) , ',',
);

I hope you get the idea.

At the end, you can run an SQL to replace multiple occurences of ',,' with an empty string to delete unnecessary commas at the end in 'sids' column.

Hi chaarmann,
Thanks for ur coding.
But i'm having very large amount of entries in my table.
So, i can't do it.
anyway,
Thanx n Regards
Yas.......

[taheer123]

good post
fantastic

[write2ashokkumar]

Hi friends,
I need to create a table which refers the fields from an existing table as follows:

Create table a (select content,sid, count(*) count from b group by content order by count desc limit 0,30)

This code works well and creates a table.
But the content field having different sid's for each value.
ex:

content sid
Apple 3
Apple 5
Apple 8
Mango 7
Mango 10.....

Is there any way to store the sid in comma separated values in mysql?

Like as follows:

content sid
Apple 3,5,8
Mango 7,10.....

Please, help me out.........

Thanx n Regards
Yas.......

Hi,

Yes, we can store the values as comma separated value. Following example explain you properly and its very simple to understand... i hope...

Example:


-- Create table
-- ============

mysql> create table content(content varchar(100),sid varchar(100));
Query OK, 0 rows affected (0.04 sec)

-- Insert the values to the table
-- ==============================

mysql> insert into content values('apple',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('apple',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('apple',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('apple',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('apple',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('mango',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into content values('mango',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('mango',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('orange',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('orange',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into content values('orange',6);
Query OK, 1 row affected (0.00 sec)

-- select the values:
-- ==================

mysql> select * from content;
+---------+------+
| content | sid |
+---------+------+
| apple | 1 |
| apple | 2 |
| apple | 3 |
| apple | 4 |
| apple | 5 |
| mango | 1 |
| mango | 2 |
| mango | 3 |
| orange | 4 |
| orange | 5 |
| orange | 6 |
+---------+------+
11 rows in set (0.00 sec)

mysql> select content as content,group_concat(sid) as sid,count(sid) as count from content group by content;
+---------+-----------+-------+
| content | sid | count |
+---------+-----------+-------+
| apple | 1,2,3,4,5 | 5 |
| mango | 3,2,1 | 3 |
| orange | 4,5,6 | 3 |
+---------+-----------+-------+
3 rows in set (0.00 sec)

-- Create table from another table
-- ===============================

mysql> create table new_content
-> select content as content,group_concat(sid) as sid,count(sid) as count from content group by content;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- select values
-- =============


mysql> select * from new_content;
+---------+-----------+-------+
| content | sid | count |
+---------+-----------+-------+
| apple | 1,2,3,4,5 | 5 |
| mango | 1,2,3 | 3 |
| orange | 4,5,6 | 3 |
+---------+-----------+-------+
3 rows in set (0.00 sec)


Regards,
S.Ashokkumar

[yasmine]

Hi,

Yes, we can store the values as comma separated value. Following example explain you properly and its very simple to understand... i hope...
...................

Regards,
S.Ashokkumar

Hi
Thanx 4 ur coding.
I tried it. but still i got [BLOB - 1.0 KB] in sid field (all values).
I read the description for GROUP_CONCAT() in the following link.
But i can't understand it properly...
Can u help me out........ It tells about the BLOB.

The link isGROUP_CONCAT()

Thanx n Regards
Yas.....

[write2ashokkumar]

Hi
Thanx 4 ur coding.
I tried it. but still i got [BLOB - 1.0 KB] in sid field (all values).
I read the description for GROUP_CONCAT() in the following link.
But i can't understand it properly...
Can u help me out........ It tells about the BLOB.

The link isGROUP_CONCAT()

Thanx n Regards
Yas.....

Hi,

You have mentioned the 2 tables a & b. can u able to give me the table structure of the b table with the field and datatype of the field.

Consider sid field is integer, If we group contact any integer values, we will get as string like '1,2,3,4,5'. so, we cant store the string value in the integer field. So while copy the table we must need to change the sid field value from integer to text/longtext/blob.

Regards,
S.Ashokkumar.

[mwasif]

yah....
i'm having the mysql version 4.1.11.

Thanks for the coding

This query works well and creates a table. But the survey id field does not contain comma separated values.
The sid field contains only [BLOB - 1.0 KB] values for all the rows.
I don't know what is it.
Can u help me.........????

Thanx n Regards
Yas........

Data is there, you need to use SUBSTRING() to view data or use some other GUI tool or command.

[yasmine]

Hi friends......

Thanks a lot.............. for ur valuable replies......
I fixed this by changing the sid field structure from integer to text. when i set text datatype to the sid field it automatically takes care on comma separation.


Special thanks to Ashok kumar.

I'll meet u people in threads soon with some other doubts

Thanx n regards
Yas....

[write2ashokkumar]

Hi friends......

Thanks a lot.............. for ur valuable replies......
I fixed this by changing the sid field structure from integer to text. when i set text datatype to the sid field it automatically takes care on comma separation.


Special thanks to Ashok kumar.

I'll meet u people in threads soon with some other doubts

Thanx n regards
Yas....

Hi yasmine,

i think, you are using the GUI tool to view the data from the table...
is it right? while using the some GUI tool, may be we will get the message "BLOB....". like this... so check with some other GUI tool like SQLyog, etc.. or use command line environment in windows / linux. we will get the full data.

i think, BLOB - 1.0 KB is not a mysql error... its a GUI tool error....

i hope, this will help u.

Regards,
S.Ashokkumar

[yasmine]

Hi yasmine,

i think, you are using the GUI tool to view the data from the table...
is it right? while using the some GUI tool, may be we will get the message "BLOB....". like this... so check with some other GUI tool like SQLyog, etc.. or use command line environment in windows / linux. we will get the full data.

i think, BLOB - 1.0 KB is not a mysql error... its a GUI tool error....

i hope, this will help u.

Regards,
S.Ashokkumar

Hi Ashok,
yes, exactly u r right.... am using phpMyAdmin.
Now i understood well on these things.
Thank U very much...........

Regards
Yas...

[yasmine]

The Correct code to do it is as follows:

CREATE TABLE a (SELECT content, cast( group_concat( survey_id ) AS char ) , count( * ) count FROM b GROUP BY content ORDER BY count DESC LIMIT 30

Thanx n Regards
Yas..