Geek asks and answers

MySQL SELECT everything into one big row (columns and rows)

I have a large MySQL SELECT query that I need to convert to only one row, so I can make an UPDATE from it without having to use two separate queries. For simplicity, suppose we have a SELECT query that returns this result:

enter image description here

How do I convert all of this into something like this:

1,Bob,20;2,Adam,30;3,Steve,40;

What can I use to UPDATE any other table with?

Knowing that the number of columns and rows can change and is not static. (very important! Especially the columns !). How can I take this off? I do not think that CONCAT()can help in this situation.

Any help would be greatly appreciated. Thank.

+5
source share
4 answers

?

SELECT group_concat(concat(id, ',', name, ',', age) separator ';')
FROM test

http://www.sqlfiddle.com/#!2/915557/9

+15

, . , .

, - table_from, _to ( , table_from ),

table_to table_from,

insert into table_to(id, name, age)
  select tf.id, tf.name, tf.age
  from table_from tf
  where tf.id != 3;

, , .

insert into table_to
  select tf.id, tf.name, tf.age
  from table_from tf
  where tf.id != 3;

_to table_from,

update table_to tt, table_from tf
  set tt.name = "Chandi"
  where tt.id = tf.id and tf.id = 1;

http://www.sqlfiddle.com/#!2/af43a/1

+2

SET @colnames := (SELECT GROUP_CONCAT(COLUMN_NAME, '\', \'') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test' AND TABLE_SCHEMA = 'db');
SET @query := CONCAT('SELECT GROUP_CONCAT(', @colnames, 'SEPARATOR \';\') FROM test');
PREPARE STMT FROM @query;
EXECUTE STMT;
+1

You can also use the CSV file. Michelle McLaughlin has a very good walk on how to do this. http://michaelmclaughlin.info/db1/lesson-7-aggregation/mysql-csv-upload/

0
source

More articles:


All Articles