MySQL GROUP_CONCAT and DISTINCT

Question

I have tb1 table like this :

name   email               link
john   [email protected]   google
john   [email protected]   facebook
john   [email protected]  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    [email protected]  twitter
john    [email protected]   facebook/google
....
....
and more

sqlfiddle

I want make result looks like :

NAME    EMAIL               SOURCE 1       SOURCE 2
john    [email protected]  twitter
john    [email protected]   facebook       google

It's possible to make result only with query?

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

Answer 1

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    [email protected]  yes         no          no
john    [email protected]   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   [email protected]   google
john   [email protected]   facebook
john   [email protected]  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.