Quote:
Originally Posted by yasmine
Hi friends,
I need to create a table which refers the fields from an existing table as follows:
Code: ( text )
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
