Importing tables into mysql with headers as row values

Question

I don't even know how to phrase this question to search it. Here's my table:

I want to put this into a table in MySQL that would look like this:

I have no idea how to automatically accomplish this. The actual table I'm using is too big to do manually like this example. Any guidance or help would be appreciated, thanks!

@Drew am I marking too many topics for closing, or what? – Shadow Feb 15 '16 at 08:30 — Shadow, Feb 15 '16 at 08:30

score 0 · Accepted Answer · answered Feb 15 '16 at 02:12

0

MySQL does not have a built in function for unpivoting, so you will have to use UNION ALL:

SELECT 'A' AS Country, Date, A As 'Amount'
FROM yourTable
UNION ALL
SELECT 'B' AS Country, Date, B As 'Amount'
FROM yourTable
 UNION ALL
SELECT 'C' AS Country, Date, C As 'Amount'
FROM yourTable
UNION ALL
SELECT 'D' AS Country, Date, D As 'Amount'
FROM yourTable
UNION ALL
SELECT 'E' AS Country, Date, E As 'Amount'
FROM yourTable

answered Feb 15 '16 at 02:12

Tim Biegeleisen

502,043
27
286
360

Thank you. I didn't know unpivoting was what I was trying to do! I used microsoft's powerquery addon to do it then just loaded my table as a CSV. – ajthyng Feb 15 '16 at 04:27

score 0 · Answer 2 · answered Feb 15 '16 at 02:21

0

SELECT 'SELECT ''' + [COLUMN_NAME] +''' as Country, Date, Amount
FROM yourTable
WHERE Country ='''   + [COLUMN_NAME] +''' UNION'
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] ='yourTable'

And than the result would be the SQL you want.

answered Feb 15 '16 at 02:21

Kason

787
3
14

Importing tables into mysql with headers as row values

2 Answers2