?
Solved

MySQL rows to columns

Posted on 2010-09-11
10
Medium Priority
?
457 Views
Last Modified: 2012-05-10
I have the following data on a table cformsdata

id | sub_date | form_id | field_name | fieild_val

2342 | 9/8/2010 | 7 | First Name | Sharon
2342 | 9/8/2010 | 7 | Last Name | Smith
2342 | 9/8/2010 | 7 | Address | 1221 N 1222 E
2342 | 9/8/2010 | 7 | City | Cresco
2342 | 9/8/2010 | 7 | State | FL
2342 | 9/8/2010 | 7 | Postal Code | 52136
2342 | 9/8/2010 | 7 | Email | myemail@email.com
2343 | 9/9/2010 | 7 | First Name | Ben
2343 | 9/9/2010 | 7 | Last Name | Lopez
2343 | 9/9/2010 | 7 | Address | 2222 N 3333 W
2343 | 9/9/2010 | 7 | City | My City
2343 | 9/9/2010 | 7 | State | TX
2343 | 9/9/2010 | 7 | Postal Code | 12345
2343 | 9/9/2010 | 7 | Email | myemail2@email.com

My desired output is

sub_date | First Name | Last Name | Address | City | State | Postal Code | Email

9/8/2010 | Sharon | Smith | 1221 N 1222 E | Cresco | FL | 52136 | myemail@email.com
9/9/2010 | Ben     | Lopez | 2222N 3333 W | My City | TX | 12345 | myemail2@email.com

How can I write an SQL Query to display my desired output to the export to Excel?
0
Comment
Question by:Benjamin_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33655917

Hi Benjamin,
You may need to look at group_concat function.

Try this in mysql

SELECT GROUP_CONCAT (id, sub_date, form_id, field_name, fieild_val)
FROM cformsdata

Kj
0
 
LVL 8

Accepted Solution

by:
kingjely earned 2000 total points
ID: 33655919
Hi,
sorry also, group by id

Try this.

SELECT GROUP_CONCAT (id, sub_date, form_id, field_name, fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655925
If you need the | in the fields, here you go..

SELECT GROUP_CONCAT (id, '  |  ' , sub_date, '  |  ' ,  form_id, '  |  ' , field_name, '  |  ' ,  fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:Benjamin_
ID: 33655926
kingjely,

When I try to run that query on MySQL Workbench I get the following error:
Error Code: 1305
FUNCTION dbname.GROUP_CONCAT does not exist
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655937
I didn't realise you were using the workbench?
Ran it in Mysql Query Browser and it works?
Do you have access to Mysql query browser?
Then you can go File>export resultset to .Csv file

0
 

Author Comment

by:Benjamin_
ID: 33655941
kingjely,

I do have access to Query Browser but i got the same error.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655948
Wierd?
In query browser, If you go to HELP> ABOUT  
I have version 1.2.17 and it works fine for me.
What version do you have?

is this your table name 'cformsdata' ?
0
 

Author Comment

by:Benjamin_
ID: 33655953
I have the same version, and yes the table name is wp_cformsdata and that is the name I am using on the query.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655979
Ahh got it, it thinks GROUP_CONCAT is a field, because,
There is a space between GROUP_CONCAT  and  (cust_name, .. should be like this...

SELECT GROUP_CONCAT(id, '  |  ' , sub_date, '  |  ' ,  form_id, '  |  ' , field_name, '  |  ' ,  fieild_val)
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;

Change the | for (,) commas if you like and try to export results to .CSV

Also should work in workbench

let me know how you go, I have to go to work in 2 mins!  ...

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655986

TO get the fields you needed, something like this, you may need to adjust the title.. but the main point it GROUP_CONCAT..  Got to fly .. have fun Ben ;)

SELECT GROUP_CONCAT(sub_date, '  |  ' , First Name, '  |  ' , Last Name, '  |  ' , Address, '  |  ' , City, '  |  ' , State, '  |  ' , Postal Code, '  |  ' , Email) as  'sub_date,    |    , First Name,    |    , Last Name,    |    , Address,    |    , City,    |    , State,    |    , Postal Code,    |    , Email'
FROM cformsdata
GROUP BY id, sub_date
ORDER BY id, sub_date;
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question