Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a table as

id    name      type           info
1     BMW       car            yes
2     Reno      car            no
3     IBM       electronics    no
4     Sony      electronics    yes
5     Mazda     car            yes

I use GROUP_CONCAT to get the list of each type, but I want to separate the concatenated column to multiple columns categorized by the column info. It should be something like

SELECT type,
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_with_info
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_without_info
       FROM table1 GROUP BY type

How can I introduce WHERE clause or using other approach to return multiple concatenated columns?

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted
SELECT type,
       GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
       GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_without_info      
FROM table1
GROUP BY type ;

Tested at SQL-Fiddle: test-1


If you wanted the results in two rows instead of one, it would be easier, just GROUP BY both type, info:

SELECT 
    type, info,
    GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
      AS list     
FROM table1
GROUP BY type
       , info ;

This can also be used to provide the one-row-per-type format you want:

SELECT
    type,
    MIN( CASE WHEN info = 'yes' THEN list END )
      AS list_with_info,
    MIN( CASE WHEN info = 'no' THEN list END )
      AS list_without_info    
FROM
    ( SELECT 
          type, info,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      GROUP BY type
             , info 
    ) AS grp 
GROUP BY type ;

Tested at SQL-Fiddle: test-2

The above two queries would benefit from an index on (type, info, name)


The following would benefit from an index on (info, type, name):

SELECT
    dt.type,
    grpy.list  AS list_with_info,
    grpn.list  AS list_without_info    
FROM
    ( SELECT DISTINCT type
      FROM table1
    ) AS dt
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'yes'
      GROUP BY type 
    ) AS grpy
      ON grpy.type = dt.type
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'no'
      GROUP BY type 
    ) AS grpn
      ON grpn.type = dt.type ;

Tested at SQL-Fiddle: test-3

share|improve this answer
    
I think the first solution is simple and elegant and well fit with my need. –  All Dec 22 '12 at 10:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.