Click here to Skip to main content
15,889,034 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have code to produce the rows that I need to then represent into columns.

Current Code is:
SQL
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!
Posted
Updated 17-Sep-15 10:30am
v2

1 solution

Maybe using PIVOT and UNPIVOT[^] will help you get a solution.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900