Unpivot table with Union Query

[illusionek]

Hi,

I am using below Union Query to unpivot attached table. Basically this is a table with some clothes and their sizes (in yellow). Now my problem is that below query works well only when all sizes are in the same row but as per attachment I may have them potentially over three different rows. I can determine which row is for which item based on size range column (in red) but I still do not know how to modify my query to look at different rows depending on value of size range column, especially that as you can see there are multiple values pointing out to the same size range i.e. 0-6 and 1-4 point out to Size Range 1.

Unfortunately I cannot change the layout of the table to have all sizes in one row :banghead:

Many thanks for all help!

Select Field1, Field2, "0" as Size,Field3 as Qty from orderform where Field3 > 0;
Union All Select Field1, Field2, "1" as Size, Field4 as Qty from orderform where Field4 > 0;
Union All Select Field1, Field2, "2" as Size, Field5 as Qty from orderform where Field5 > 0;
Union All Select Field1, Field2, "3" as Size, Field6 as Qty from orderform where Field6 > 0;
Union All Select Field1, Field2, "4" as Size, Field7 as Qty from orderform where Field7 > 0;
Union All Select Field1, Field2, "5" as Size, Field8 as Qty from orderform where Field8 > 0;
Union All Select Field1, Field2, "6" as Size, Field9 as Qty from orderform where Field9 > 0;

 

[namliam]

Where from and how do you get your data? text file?excel file? etc?

These kind of pivots usually are exports from someplace, any chance of getting at the source data?

 

[illusionek]

This is from the attached Excel file. Basically this is an order form completed by the customer. Then I need to generate a full Item Name to upload it onto the system i.e. AB1 and corresponding quantity. So there is no data source to use.

 

[namliam]

THe exact excel sheet you linked? Wow...

 

[illusionek]

That's the one. Any ideas?

 

[namliam]

So how should I interpert the sheet? for example Item No BB
1-4 and 8 and 6

Does the customer want 8 of size 0 and 6 of size 5?
or 8 of size 1 but then the 6 of size 6? Or 6 of size 4?

The current size ranges dont really seem to match with the orders?

 

[illusionek]

Hi

Based on the attached previously table, I would like to see following result:

Item No | Size | Qty
AB | XS | 1
AB | S | 1
AD | 27 | 2
AD | 31 | 3
BB | 0 | 8
BB | 5 | 6
BC | 3 | 2


I hope it makes more sense now.

Please help if you can

 

[namliam]

How would you identify AB > XS,1 and S, 1 ??

I mean, S-L, M-XL and XS-M all refer to Size range 2 and 0-6, 1-4 and 2-6 and all the different options all point to range 1...
How so we result this piece of the puzzle?

 

[illusionek]

Hi

Please look at the table I attached in my first post.

I just look at column Size Range to see to which Size Range a row refers to i.e. Size Range 2 for AB.

Then I look at quantities in the table and I see that '1' is under column XS in Size Range 2 and another '1' is under S.

Then if I look for example at AD, which refers to Size Range 3 and I see that there are two units for size 27 and 3 units for size 31.

I hope this makes more sense now.

 

[namliam]

I understand your logic of looking at the column rows above to find the amounts, however.... How would a database, a program do this?

For exampl at AD the size range says 25-35, not OS-35
While at BA it says 0-6, which would be a "proper" size range... for BB it is 1-4 and BC 1-6, BD 2-6, BH 0-5. AB is S-L while AE is XS-M and AG M-XL.
How can we tell code to look for different things and do the same thing?
We could take a random number look it up and match it to a size range, then map the orders but this isnt the way it is usually done....
You would normaly have a "fixed" size range, if that isnt doable then the random part would be the way to go... Though a little harder.