Geek Answers Handbook

MySQL: output multiple values ​​at once

I think I have done this before, but it could also be a PHP function. I would like to make a MySQL query (in a MySQL client, not PHP) and get for example

Foo    A
       B
       C
Bar    B
       D
       E

Instead

Foo    A
Foo    B
Foo    C
Bar    B
Bar    D
Bar    E

This, of course, will only make sense if it was ordered by this first column. I’m not sure that this is possible, but, as I said, I would like to remember that I did it once, but I can’t remember how or through some PHP “magic” ...

Update: I suddenly remembered where I used it. What I was thinking was a modifier WITH ROLLUPfor GROUP BY. But I also found that he is not doing what I think here, so my question is still standing. Although I do not think that there is a solution now. But smart people proved that I was wrong before: P

: , , " ". Foo , . A, B, C, D, E - , .

attendee (id, first_name, last_name, ...)
attendees_options (attendee_id, option_id)
option (id, name, description)
+5
2

Foo    A,B,C
Bar    B,D,E    

SELECT column1, GROUP_CONCAT(column2) FROM table GROUP BY column1
+1

SQL Server, , MySQL.

create table test (
    id int,
    col1 char(3),
    col2 char(1)
)

insert into test
    (id, col1, col2)
    select 1, 'Foo', 'A' union all
    select 2, 'Foo', 'B' union all
    select 3, 'Foo', 'C' union all
    select 4, 'Bar', 'D' union all
    select 5, 'Bar', 'E' union all
    select 6, 'Bar', 'F'

select case when t.id = (select top 1 t2.id 
                             from test t2 
                             where t2.col1 = t.col1 
                             order by t2.col1, t2.col2) 
            then t.col1
            else ''
       end as col1, 
       t.col2
    from test t
    order by t.id

drop table test
0

More articles:


All Articles