SQL TOP N - 多行到多列 [英] SQL TOP N - Multiple rows into multiple columns
本文介绍了SQL TOP N - 多行到多列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有代码来生成我需要在列中表示的行。
当前代码为：
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
请注意，并非所有商品都有5位客户购买它们，因此前5位客户只能生产1-4位客户......这些额外的栏目将保持空白。 br />
此外，只显示前三列，但确实需要前5名。
任何想法都会是非常感谢！
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!
推荐答案
也许正在使用 PIVOT和UNPIVOT [ ^ ]将帮助您获得解决方案。
Maybe using PIVOT and UNPIVOT[^] will help you get a solution.
这篇关于SQL TOP N - 多行到多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
