CODEWITHSUNDEEP
mysqlsqldatabasecsvselect
nehem
nehem

Reputation: 13662

How to select columns as CSV in MySQL?

Here is the table

id    name
----------
1     john
2     dave
3     eve

select * from table will give us 

id    name
----------
1     john
2     dave
3     eve

How will you query in order to get the below output?

id       name
----------------------
1,2,3    john,dave,eve

Upvotes: 3

Views: 3378

Answers (3)

Aman Aggarwal
Aman Aggarwal

Reputation: 18469

You need to first increase the value of variable group_concat_max_len so that data shouldn't truncate in group_concat . Check the variable values by command:

show variables like 'group_concat_max_len'

And increase it as:

set global group_concat_max_len = 160000.

After this use query:

SELECT GROUP_CONCAT(id), GROUP_CONCAT(name) FROM   table_name

Upvotes: 7

Mureinik
Mureinik

Reputation: 311978

You could use the group_concat function:

SELECT GROUP_CONCAT(id), GROUP_CONCAT(name)
FROM   mytable

Upvotes: 4

ACV
ACV

Reputation: 10560

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

Upvotes: 3

Related Questions