0

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
WGroleau
  • 448
  • 1
  • 9
  • 26

1 Answers1

1

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

Surberus
  • 226
  • 1
  • 6
  • Yes, but that's not "an easy way." That's more typing than writing a nested loop in VBA to do it. I could even cut&paste cells faster than typing the above. – WGroleau Sep 05 '16 at 03:16
  • Updated with link to another question giving step by step instructions in excel. – Surberus Sep 05 '16 at 03:46
  • Well, technically it's not an "easy way in mySQL," but since it gets the job down, it qualifies as an answer. :-) Only, it pretty much makes my question a duplicate. – WGroleau Sep 05 '16 at 03:58