Solved

MySQL rows to columns

Posted on 2010-09-11
10
443 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_
  • 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 500 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 43
mysql ide 10 40
when to use sequences in mysql 4 30
Insert values are dynamic 11 43
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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