I have code to produce the rows that I need to then represent into columns.
Current Code is:
SELECT l.item_no As Item, l.cus_no AS Customer, SUM(l.qty_to_ship) AS QtySold, COUNT(l.qty_to_ship) AS FreqSold, AVG(l.qty_to_ship) AS AvgSold
FROM oelinhst_sql l
LEFT OUTER JOIN oehdrhst_sql h ON h.ord_type = l.ord_type AND h.ord_no = l.ord_no AND h.inv_no = l.inv_no
WHERE l.item_no IN
(SELECT TOP 5 l2.item_no FROM oelinhst_sql l2 WHERE l2.item_no = l.item_no AND h.inv_dt >= '9/1/2015' AND h.inv_dt <= '9/30/2015' ORDER BY l2.item_no)
AND h.inv_dt >= '9/1/2015' AND h.inv_dt <= '9/30/2015'
GROUP BY l.item_no, l.cus_no
ORDER BY l.item_no, SUM(l.qty_to_ship) DESC
Which will produce:
Item Customer QtySold FreqSold AvgSold
100238 TAS 173 1 173
100238 SCL 14 1 14
100238 AJG 12 1 12
100240 AJG 15 1 15
100243 TAS 200 1 200
100243 AJG 16 1 16
100252 AJG 12 1 12
100260 AJG 12 1 12
100320 AJG 10 1 10
100324 AJG 10 1 10
100325 AJG 10 1 10
100329 AJG 10 1 10
100488 AJG 10 1 10
100508 NBK 2 1 2
100512 NBK 6 1 6
100907 AJG 22 1 22
102314 SCK 3 1 3
103093 SCK 15 1 15
103205 HOU 7 1 7
What I need to produce from that is something like this:
Item Cust 1 Qty 1 Freq 1 Avg 1 Cust 2 Qty 2 Freq 2 Avg 2 Cust 3 Qty 3 Freq 3 Avg 3
100238 TAS 173 1 173 SCL 14 1 14 AJG 12 1 12
100240 AJG 12 1 15
100243 TAS 200 1 200 AJG 16 1 16
Note that not all items have 5 customers buying them so the top 5 may yield only 1-4 customers...those extra columns will remain blank.
Also, only showing Columns above for top 3 but really need top 5.
Any ideas would be greatly appreciated!