Crosstab query (2)

Part one here.

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.

 

Advertisements
This entry was posted in SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s