MySQL GROUP_CONCAT and DISTINCT

Question

I have tb1 table like this :

name   email               link
john   myemail@gmail.com   google
john   myemail@gmail.com   facebook
john   myemail2@gmail.com  twitter
....
....
and more

When I call data with query looks like

SELECT name, email, group_concat(DISTINCT link SEPARATOR '/') as source
FROM tb1
group by email

And result Like this :

NAME    EMAIL               SOURCE
john    myemail2@gmail.com  twitter
john    myemail@gmail.com   facebook/google
....
....
and more

sqlfiddle

I want make result looks like :

NAME    EMAIL               SOURCE 1       SOURCE 2
john    myemail2@gmail.com  twitter
john    myemail@gmail.com   facebook       google

It's possible to make result only with query?

Note : I want to make dynamic column source 1, 2, 3 ...., n

That's a variation pivot table (In theory, Source 1, Source 2.... can continue to Source X based on X number of distinct Source values, right?) If so, you should search for "mysql pivot table", there are some great (albeit non-trivial) articles — AgRizzo, Oct 08 '13 at 10:02
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — ModulusJoe, Oct 08 '13 at 10:06
thanks @AgRizzo for ref, I will read about mysql pivot table. This question can hold. I will update my question when I stuck with mysql pivot table. — itx, Oct 08 '13 at 10:06

score 2 · Answer 1 · answered Oct 08 '13 at 10:35

My answer is, don't do it!

What you want to do is called pivoting. In MySQL this is usually done with one hell of a

....
aggregate_function(case when ...),
aggregate_function(case when ...),
aggregate_function(case when ...),
...

block. But since you don't know all your sources, you'd have to write a procedure to dynamically build the statement for you and execute it. And even then you'd end up with a table structure like

NAME    EMAIL               twitter     facebook    google
----------------------------------------------------------
john    myemail2@gmail.com  yes         no          no
john    myemail@gmail.com   no          yes         yes

So, how would the programmers of your application know how the columns are named?

To avoid this you'd have to do again one hell of a programming in your stored procedure to put the row values of each email into one column. Then how would you determine, which row gets into which column? What if facebook appears in one row in column source1 and in another row in column source2? And in your application you'd have to test or otherwise determine how much columns there are actually, or can each user/email only have 2 sources?

To sum things up, a result like this

name   email               link
john   myemail@gmail.com   google
john   myemail@gmail.com   facebook
john   myemail2@gmail.com  twitter

is perfectly fine and it's up to the application developers to deal with it. And it's much, much easier to do so for them than for the database guy in your team.

score 1 · Answer 2 · answered Oct 08 '13 at 10:05

1

No you can't since with MySQL you can not create dynamically additional column... (this is more an olap stuff or wich allow such functionality called pivot table)

answered Oct 08 '13 at 10:05

nemenems

1,064
2
9
27

1

he never mentioned dinamic – AdrianBR Oct 08 '13 at 10:26
@AdrianBR if its not dynamic then my answer can work, but asker need to verify it. – Ankit Sharma Oct 08 '13 at 10:30

Ankit Sharma · Answer 3 · 2013-10-08T10:30:01.803

It can work for this case, but its not a dynamic query and can work only if there are maximum of 2 link present for same email.

SELECT `name`, email, 
substring_index(group_concat(DISTINCT link SEPARATOR '/'),'/',1) as source_1,
if(group_concat(DISTINCT link SEPARATOR '/') like '%/%',
substring_index(group_concat(DISTINCT link SEPARATOR '/'),'/',-1),'') as source_2
FROM test2 group by email

If you know the exact number of possibility of links present per email then the same query can be modify accordingly.

MySQL GROUP_CONCAT and DISTINCT

3 Answers3