CODEWITHSUNDEEP
mysqlexport
tdog4224
tdog4224

Reputation: 150

How to export data when the fieldnames are stored in one table and the data in another

I have two tables:

Table field stores data of form field names (e. g.: Name, Age, Email each as one Dataset):

id | formName | filedName
1  | 1        | name
2  | 1        | age
3  | 2        | name
4  | 2        | age

Table data stores data for each field:

id | fieldName | userID |data
1  | 1         | 1      |Peter
2  | 1         | 2      |Steve
3  | 2         | 1      |24
4  | 2         | 2      |34

I want to export the data as CSV from phpMyAdmin in a way that gives me the data from field as column names and the data from data as the specific value. It should look something like this:

name  | age
Peter | 24
Steve | 34

What is the query for that? I know it is possible since I already did this a while back, cannot remember the syntax though.

Upvotes: 0

Views: 36

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6854

If you want to get only name and age then you can get your desired results from your data_table only otherwise you can also join field table also. Please try below query and let me know if you think that you need some thing else.

select a.data as 'Name',b.data as 'Age' 
from (select userid,data from tablename where fieldname=1) a 
join (select userid,data from tablename where fieldname=2) b 
on a.userid=b.userid;

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

You can do this simply using MAX and GROUP BY:

SELECT MAX(CASE WHEN fieldName=1 THEN data END) as name,
       MAX(CASE WHEN fieldName=2 THEN data END) as age
FROM TableName
GROUP BY userID

Result:

NAME    AGE
Peter   24
Steve   34

See result in SQL Fiddle.

Upvotes: 1

Related Questions