Another way to do vertical to horizontal query. Let’s have a table with sales_id, prod_id, sales_date, and sales_amount fields. This query will return 4 latest sales_id in 3 columns:
SELECT prod_id, (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) as sales_id1,
(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id2,
(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 2 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3,
(SELECT TOP 1 sales_id FROM table WHERE prod_id=s.prod_id AND sales_id NOT IN (SELECT TOP 3 sales_id FROM table WHERE prod_id=s.prod_id ORDER BY sales_date) ORDER BY sales_date) as sales_id3
FROM table as s GROUP BY prod_id
Then you can join this view/sub-query to get detailed data for each row.
