how to convert date from rows to columns as week numbers and get the price from the highest week number

1
votes

Problem: I am trying to convert date from rows to columns as week numbers and get the price from the highest week number and call it givenPrice.

enter image description here

expected:

enter image description here

1
sqlhivehql

1 Answers

0
votes

See below. In your case, shopName is the row field, date is your pivot field, and price is your crosstab field. Since it appears that you are not doing anything to the crosstab values, we can just use a Max() function as a dummy because we don't need to ensure that the values in the pivot field are unique. So, I came up with this:

TRANSFORM Max(price)
SELECT shopName
FROM YourTable
GROUP BY shopName
PIVOT date;

NOTE: This query is Access SQL.

https://i.stack.imgur.com/492Lb.png