CODEWITHSUNDEEP
sqlms-access
Steph Stoudenmire
Steph Stoudenmire

Reputation: 1

SQL syntax for ms-access query to pivot column

I am trying to organize data from previous sales for my company right now it looks like this:

Item1    Item2
Clock    Spring
Hands    Nuts
Dial     Clock
Clock    Hands
Hands    Spring

what I need is this

Item1   Product1   Product2
Clock   Spring    Hands
Hands   Nuts      Spring
Dial    Clock

I have about 1000 data points so I would like something dynamic but I got this far:

SELECT transdata.item1, Product1, Product2, Product3
(
SELECT transdata.item1, transdata.item2,
     'Product' + CAST(ROW_NUMBER() over (Partition By transdata.[item1] Order By transdata.item1) AS Varchar(10)) AS ColSequence
FROM transdata
) Temp

PIVOT
(
MAX(Product)
For ColSequence in (Product1, Product2, Product3)
)PIV

It says I have a syntax error

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This can be really painful in MS Access. But this relatively simple version works for at most two columns:

select item1, min(item2) as product1,
       iif(min(item2) <> max(item2), max(item2), null) as product2
from transdata
group by item1

Upvotes: 1

Related Questions