Excel, SQL - Change select header rows to columns and insert values for each column

Question

I hope someone can help. I have an excel file with hundreds of items that looks like the below table, that shows the Style's Color/Size and it's QTY levels. But I need to take the corresponding header Sizes and match it with the Style and Color as well as it's QTY levels. Then format it into each row and then copy the Style, color, length and price that corresponds with each columns. I have both excel and sql, if one is easier to work with then the other.

So basically take this:

+--------+----------+--------+-------+---------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| STYLE# |  COLOR   | LENGTH | Price | Size 00 | Size 0 | Size 2 | Size 4 | Size 6 | Size 8 | Size 10 | Size 12 | Size 14 | Size 16 | Size 18 | Size 20 | Size 22 | Size 24 | Size 26 | Size 28 | Size 30 | Size 32 |
+--------+----------+--------+-------+---------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|    710 | PURPLE   | RL     |   199 |       0 |      0 |      0 |      2 |      5 |      5 |       5 |       4 |       4 |       3 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |
|    710 | DP CORAL | RL     |   199 |       0 |      0 |      2 |      0 |      1 |      2 |       1 |       3 |       1 |       3 |       1 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |
|    720 | RED      | RL     |   225 |       0 |      0 |      1 |      0 |      0 |      0 |       1 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |
|    720 | NAVY     | RL     |   225 |       0 |      0 |      0 |      1 |      0 |      1 |       2 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |       0 |
+--------+----------+--------+-------+---------+--------+--------+--------+--------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

And turn it into this:

+--------+----------+---------+-----+--------+-------+
| STYLE# |  COLOR   |  Size   | QTY | LENGTH | Price |
+--------+----------+---------+-----+--------+-------+
|    710 | PURPLE   | Size 00 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 0  |   0 | RL     |   199 |
|    710 | PURPLE   | Size 2  |   0 | RL     |   199 |
|    710 | PURPLE   | Size 4  |   2 | RL     |   199 |
|    710 | PURPLE   | Size 6  |   5 | RL     |   199 |
|    710 | PURPLE   | Size 8  |   5 | RL     |   199 |
|    710 | PURPLE   | Size 10 |   5 | RL     |   199 |
|    710 | PURPLE   | Size 12 |   4 | RL     |   199 |
|    710 | PURPLE   | Size 14 |   4 | RL     |   199 |
|    710 | PURPLE   | Size 16 |   3 | RL     |   199 |
|    710 | PURPLE   | Size 18 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 20 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 22 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 24 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 26 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 28 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 30 |   0 | RL     |   199 |
|    710 | PURPLE   | Size 32 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 00 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 0  |   0 | RL     |   199 |
|    710 | DP CORAL | Size 2  |   2 | RL     |   199 |
|    710 | DP CORAL | Size 4  |   0 | RL     |   199 |
|    710 | DP CORAL | Size 6  |   1 | RL     |   199 |
|    710 | DP CORAL | Size 8  |   2 | RL     |   199 |
|    710 | DP CORAL | Size 10 |   1 | RL     |   199 |
|    710 | DP CORAL | Size 12 |   3 | RL     |   199 |
|    710 | DP CORAL | Size 14 |   1 | RL     |   199 |
|    710 | DP CORAL | Size 16 |   3 | RL     |   199 |
|    710 | DP CORAL | Size 18 |   1 | RL     |   199 |
|    710 | DP CORAL | Size 20 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 22 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 24 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 26 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 28 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 30 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 32 |   0 | RL     |   199 |
|    710 | DP CORAL | Size 00 |   0 | RL     |   199 |
|    720 | RED      | Size 0  |   0 | RL     |   225 |
|    720 | RED      | Size 2  |   1 | RL     |   225 |
|    720 | RED      | Size 4  |   0 | RL     |   225 |
|    720 | RED      | Size 6  |   0 | RL     |   225 |
|    720 | RED      | Size 8  |   0 | RL     |   225 |
|    720 | RED      | Size 10 |   1 | RL     |   225 |
|    720 | RED      | Size 12 |   0 | RL     |   225 |
|    720 | RED      | Size 14 |   0 | RL     |   225 |
|    720 | RED      | Size 16 |   0 | RL     |   225 |
|    720 | RED      | Size 18 |   0 | RL     |   225 |
|    720 | RED      | Size 20 |   0 | RL     |   225 |
|    720 | RED      | Size 22 |   0 | RL     |   225 |
|    720 | RED      | Size 24 |   0 | RL     |   225 |
|    720 | RED      | Size 26 |   0 | RL     |   225 |
|    720 | RED      | Size 28 |   0 | RL     |   225 |
|    720 | RED      | Size 30 |   0 | RL     |   225 |
|    720 | RED      | Size 32 |   0 | RL     |   225 |
|    720 | NAVY     | Size 00 |   0 | RL     |   225 |
|    720 | NAVY     | Size 0  |   0 | RL     |   225 |
|    720 | NAVY     | Size 2  |   0 | RL     |   225 |
|    720 | NAVY     | Size 4  |   1 | RL     |   225 |
|    720 | NAVY     | Size 6  |   0 | RL     |   225 |
|    720 | NAVY     | Size 8  |   1 | RL     |   225 |
|    720 | NAVY     | Size 10 |   2 | RL     |   225 |
|    720 | NAVY     | Size 12 |   0 | RL     |   225 |
|    720 | NAVY     | Size 14 |   0 | RL     |   225 |
|    720 | NAVY     | Size 16 |   0 | RL     |   225 |
|    720 | NAVY     | Size 18 |   0 | RL     |   225 |
|    720 | NAVY     | Size 20 |   0 | RL     |   225 |
|    720 | NAVY     | Size 22 |   0 | RL     |   225 |
|    720 | NAVY     | Size 24 |   0 | RL     |   225 |
|    720 | NAVY     | Size 26 |   0 | RL     |   225 |
|    720 | NAVY     | Size 28 |   0 | RL     |   225 |
|    720 | NAVY     | Size 30 |   0 | RL     |   225 |
|    720 | NAVY     | Size 32 |   0 | RL     |   225 |
+--------+----------+---------+-----+--------+-------+

Any help on this would be greatly appreciated.

This is a [pivot table](http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/) — crthompson, Mar 19 '14 at 01:00
Please note I still need to copy the Style#, Color, Price, and QTY from each row per each size header columns. — otasi, Mar 19 '14 at 02:05
@paqogomez I'm not very good with sql, but this doesn't seem to be a good solution as I would have to 'sum' up all my color and style values. Which with hundreds of records is going to take forever. — otasi, Mar 19 '14 at 02:54
Forever is a very long time. I doubt it will take that long. "hundreds of records" ?!? I would be worried if there were millions....not hundreds. — crthompson, Mar 19 '14 at 03:41

score 0 · Answer 1 · answered Mar 19 '14 at 01:03

0

In SQL you can do something like this I think(sorry I am a little rusty)..

SELECT style, color, size, qty, length price, 
FROM table_name
ORDER BY style DESC, color, size DESC, qty DESC, length, price;

answered Mar 19 '14 at 01:03

BRBT

1,467
8
28
48

This just orders this by desc order. – otasi Mar 19 '14 at 02:26

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

In excel you can do this step (as i think is the fast way) :

choose your data to become rows
copy that data
right click
choose paste special and you choose transpose

In sql server you can use keyword Pivot and Unpivot this link may help you for this :

Transpose in SQL Server

edited May 23 '17 at 12:27

Community

1
1

answered Mar 19 '14 at 01:03

Deddy H

252
2
6
19

I'm not very good with sql, but this doesn't seem to be a good solution as I would have to 'sum' up all my color and style values. Which with hundreds of records is going to take forever. – otasi Mar 19 '14 at 02:55

score 0 · Accepted Answer · answered Mar 20 '14 at 17:35

I was able to figure out what I need by just using excel and joining the color, price, then adding the corresponding size to each qty level with a pipe-delimiter separating each value. Then transposing it vertically then, using the text to column function in excel to separate everything out.

Excel, SQL - Change select header rows to columns and insert values for each column

3 Answers3