CODEWITHSUNDEEP
mysqlgroup-by
Chris P
Chris P

Reputation: 2357

Mysql merge rows

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

Upvotes: 5

Views: 11306

Answers (5)

Mihai Matei
Mihai Matei

Reputation: 24276

Try this:

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

SQL Fiddle

Upvotes: 16

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 0

karthzDIGI
karthzDIGI

Reputation: 403

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

Upvotes: 1

Harshil
Harshil

Reputation: 411

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 )

Upvotes: 0

Mayukh Roy
Mayukh Roy

Reputation: 1815

Try this:

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

SQL FIDDLE HERE

Upvotes: 0

Related Questions