WGroleau WGroleau - 3 months ago 18
MySQL Question

mySQL, two-dimensional into one-dimensional

I've forgotten whatever I used to know about pivots, but this seems to me the reverse. Suppose I have a set of items A, B, C, D, … and a list of attributes W, X, Y, Z. I have in a spreadsheet something like

A B C D
W 1 P 3 Q
X 5 R 7 S
Y T 2 U 4
Z D 6 F 7


where the value of attribute X for item B is 'P'. In order to do some statistics on comparisons, I'd like to change it from table to list, i.e.,

W A 1
X A 5
Y A T
Z A D
W B P
X B R
Y C U
Z C F
W D Q
X D S
Y B 2
Z B 6
Etc.


I can easily write a nested loop macro in the spreadsheet to do it, but is there an easy way to import it into mySQL in the desired format? Queries to get the statistics needed are simple in SQL (and formulas not very hard in a spreadsheet) if the data is in the second format.

Since there apparently isn't a "spreadsheet" tag, I used "excel." :-)

There are a lot of questions that looked similar at first glance, but the five I looked at all wanted to discard one of the indices (A-D or W-Z), i.e. creating something like

W 1
W P
X 5
X R

Answer

EDITED

You can use PowerQuery to unpivot tables. See the answer by teylyn for the following question. I have Office 365 and didn't need to install the plugin first. The functionality was already available.

Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

Another way to unpivot data without using VBA is with PowerQuery, a free add-in for Excel 2010 and higher, available here: http://www.microsoft.com/en-us/download/details.aspx?id=39379

...

Click the column header of the first column to select it. Then, on the Transform ribbon, click the Unpivot Columns drop-down and select Unpivot other columns.

...

OLD ANSWER

If you import the spreadsheet as is, you can run a query to output the correct format. For a fixed, small number of items, you can use UNION for each of the columns.

SELECT attr, 'A' AS 'item', A AS 'value'
FROM sheet
UNION
SELECT attr, 'B' AS 'item', B AS 'value'
FROM sheet
UNION
SELECT attr, 'C' AS 'item', C AS 'value'
FROM sheet
UNION
SELECT attr, 'D' AS 'item', D AS 'value'
FROM sheet;

Working example: http://sqlfiddle.com/#!9/c274e7/7