Mysql merge rows

Question

I have this table:

ip   country

1     A
2     A
3     B
4     B
4     B

I am trying to write a query, which will return something like:

A 1,2
B 3,4

E.g. SELECT * FROM table GROUP BY country returns:

A 1
B 3

But it's not the desired result.

Ι can run this simple query:

SELECT * FROM table ORDER BY ip

and programmatically will write something like:

$c_ip=0;
while($row=mysql_fetch_array($result,MYSQL_ASSOC)){
if($row['ip'])!=$c_ip)
{
$c_ip=$row['ip'];
//new line!!
}else
{
//don't close <tr> code goes here
}
}

Possible [Duplicate](http://stackoverflow.com/questions/14065277/sql-select-multiple-rows-in-one-column) — Prahalad Gaggar, Apr 02 '13 at 09:54

Mihai Matei · Accepted Answer · 2016-01-29T07:49:17.243

16

Try this:

SELECT country, GROUP_CONCAT(DISTINCT ip SEPARATOR ',') AS ips 
FROM my_table 
GROUP BY country

SQL Fiddle

edited Jan 29 '16 at 07:49

answered Apr 02 '13 at 09:44

Mihai Matei

24,166
5
32
50

2

According to SQL Fiddle it doesn't gives the correct answer. (4 is repeated) – Prahalad Gaggar Apr 02 '13 at 09:58

score 1 · Answer 2 · answered Apr 02 '13 at 09:58

1

You may need to add 'DISTINCT' infront of ip, if you don't want the value 4 to be repeated.

SELECT country, GROUP_CONCAT(DISTINCT ip SEPARATOR ',') AS ids 
FROM my_table 
GROUP BY country

answered Apr 02 '13 at 09:58

karthzDIGI

393
1
4
15

Prahalad Gaggar · Answer 3 · 2013-04-02T10:25:23.127

0

Try this Query, you can use STUFF in SQL SERVER

   SELECT 
  t1.country,
  STUFF((
    SELECT distinct ', ' + cast(t2.ip as varchar)
    FROM Table1 t2
    WHERE t2.country = t1.country
    FOR XML PATH (''))
  ,1,2,'') AS Names
FROM Table1 t1
GROUP BY t1.country;

SQL FIDDLE

edited Apr 02 '13 at 10:25

answered Apr 02 '13 at 09:45

Prahalad Gaggar

11,389
16
53
71

score 0 · Answer 4 · answered Apr 02 '13 at 09:45

0

Try this:

SELECT country, GROUP_CONCAT(ip)
FROM table1
GROUP BY country

SQL FIDDLE HERE

answered Apr 02 '13 at 09:45

Mayukh Roy

1,815
3
19
31

score 0 · Answer 5 · answered Apr 02 '13 at 09:47

Try this

              SELECT DISTINCT country, SUBSTRING(ConcateColumn,2,LEN(ConcateColumn))
    FROM Table t1
   CROSS APPLY ( 
                select substring((SELECT  ','  + ip 
                     FROM Table t2
                     WHERE t2.country = t2.country
                     ORDER BY DescOfFault 
                     FOR XML PATH('')),2,20000)
                      )  D ( ConcateColumn )

Mysql merge rows

5 Answers5