1

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.

Dean Winsbury
  • 23
  • 7
  • 1
    Possible duplicate of [SQL Unique Values Numbering Sequence for Pivot](https://stackoverflow.com/questions/1110931/sql-unique-values-numbering-sequence-for-pivot) – Andre Feb 11 '18 at 19:30
  • I vaguely remember a question last year or so, that would be a better duplicate target, but I don't find it. :( But yes, you need a column (stored or calculated) to determine the column index of a pivot value. – Andre Feb 11 '18 at 19:32
  • I tried to have a go at this but could not get it to work, but perhaps someone else might, or it might give you a direction to try. I attempted to include a row count in your sub-query but Access gives me errors about not recognising the t1 fields in the sub-query and I'm not sure why yet. I also tried moving it to its own query (which works on its own) but when called from the crosstab query it fails with the same error. cont... – andrew Feb 12 '18 at 03:05
  • ...cont... `TRANSFORM First(t3.Itemimage) SELECT t3.Itemcode FROM ( SELECT t1.Itemcode, (SELECT COUNT(*) + 1 FROM Table1 t2 WHERE t1.Itemcode = t2.Itemcode and t2.Itemimage < t1.Itemimage) AS [Index], t1.Itemimage FROM Table1 AS t1) AS t3 GROUP BY t3.Itemcode PIVOT t3.Index` – andrew Feb 12 '18 at 03:05
  • An alternative is to use the sub query with the row count to populate a temp table which you can then use in the crosstab query. It's easier than constructing the final output in VBA (though you'd need to use VBA or macros to clear and populate the temp table). – andrew Feb 12 '18 at 03:07
  • Thanks Andre, yes that thread has a similar issue but remains unresolved. Your code looks like a step in the right direction. I get the same errors so tried creating the temp table as you suggest but notice that the sequence of the itemimage field is changed from the original table1. – Dean Winsbury Feb 12 '18 at 12:19
  • SELECT t1.Itemcode, (SELECT COUNT(*) + 1 FROM Table1 t2 WHERE t1.Itemcode = t2.Itemcode and t2.Itemimage < t1.Itemimage) AS [Index], t1.Itemimage FROM Table1 AS t1; Itemcode Index Itemimage 12345 1 image-a.jpg 12345 2 image-b.jpg 23456 2 image-c.jpg (expecting index =1) 23456 3 image-d.jpg (expecting index =2) 23456 1 image-a.jpg (expecting index =3) 34567 1 image-e.jpg – Dean Winsbury Feb 12 '18 at 12:29

0 Answers0