I'm really struggling to 'Transform' or pivot this simple two column table, is there a way ?
All the solutions I have found so far ( pivot / transform / crosstab etc) require the data to have some kind of third field index number in order to create the column names. In my case each Itemcode can have any number of Itemimage and those itemimage sometimes are valid for more than one Itemcode. Typically the number of itemimage will be less than 20 but there are tens of thousands of Itemcode.
transform Table1:
Itemcode Itemimage
12345 image-a.jpg
12345 image-b.jpg
23456 image-c.jpg
23456 image-d.jpg
23456 image-a.jpg
34567 image-e.jpg
45678 image-a.jpg
into a table like this:
Itemcode Itemimage1 Itemimage2 Itemimage3
12345 image-a.jpg image-b.jpg null
23456 image-c.jpg image-d.jpg image-a.jpg
34567 image-e.jpg null null
45678 image-a.jpg null null
The nearest I have come to getting this to work is
TRANSFORM First(MySubQuery.Itemimage) AS FirstOfItemimage
SELECT MySubQuery.Itemcode
FROM (SELECT [Itemcode], 'Itemimage' & [Number] AS ColumnName, [Itemimage] FROM Table1) AS MySubQuery
GROUP BY MySubQuery.Itemcode
PIVOT MySubQuery.ColumnName;
but this requires the creation of a [Number] column in Table1 to create the incremental index number for the columns.
day 3 of working on this ... beginning to think it cannot be done with a single query and will require a Visual Basic macro to sort into Itemcode order then create an array which is read out to a new table. Really need some help as I have never programed in VB.
note: it is important that the resulting table is in the same order as Table1 This is because the data in Table1 is in a specific order as provided to me by a 3rd party. The sample data above shows only the Itemimage field but I have similar data with Itemdescription containing sentences where the sequence must be maintained in order to keep its grammatical meaning unchanged.
