how to 'unpivot' table

[illusionek]

Hi Guys,

I have been trying to solve below problem for couple weeks now without much luck. So I do really hope someone could help me.

I import from Excel attached table.

I need now somehow 'unpivot' this table and in order to do it I need to concatenate columns and rows names and then match correct values so based on the above example, I would need to receive table with the following two columns:

Item X Size 1 | 0
Item X Size 2 | 4
Item X Size 3 | 6
Item Y Size 1 | 0

etc

I have been trying various solutions found via Google but nothing seems to be working.

Please help ...

 

[plog]

You will need a UNION query (http://www.techonthenet.com/sql/union.php). Essentially, in that UNION query you write one SELECT statement for each row you want to 'unpivot'. Like this:

SELECT ItemName, "Size 1" As Size, [Size 1] As Qty
FROM YourTableNameHere
UNION ALL 
SELECT ItemName, "Size 2" As Size, [Size 2] As Qty
FROM YourTableNameHere
UNION ALL
SELECT ItemName, "Size 3" As Size, [Size 3] As Qty
FROM YourTableNameHere
.
.
.

 

[namliam]

A union query will work if you have few columns, if your table has many columns and/or you are importing the table to a normalized access table you may be better of using single append queries per column.

Worse come to worse you can also write some VBA to transpose the table column by column but most likely either the Union or Append query (or a combination of the two) will be the best solution unless you need some additional specific work done.

 

[illusionek]

Many thanks, UNION query did the trick!