用于生成矩阵的 SQL 查询，如在 SQL Server 中查询相关表的输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5216701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 09:36:23 来源:igfitidea点击:
SQL Query for generating matrix like output querying related table in SQL Server
提问by Nagesh
I have three tables:
Product
我有三个表：
产品
ProductID ProductName
1 Cycle
2 Scooter
3 Car
Customer
顾客
CustomerID CustomerName
101 Ronald
102 Michelle
103 Armstrong
104 Schmidt
105 Peterson
Transactions
交易
TID ProductID CustomerID TranDate Amount
10001 1 101 01-Jan-11 25000.00
10002 2 101 02-Jan-11 98547.52
10003 1 102 03-Feb-11 15000.00
10004 3 102 07-Jan-11 36571.85
10005 2 105 09-Feb-11 82658.23
10006 2 104 10-Feb-11 54000.25
10007 3 103 20-Feb-11 80115.50
10008 3 104 22-Feb-11 45000.65
I have written a query to group the transactions like this:
我编写了一个查询来对这样的交易进行分组：
SELECT P.ProductName AS Product,
C.CustName AS Customer,
SUM(T.Amount) AS Amount
FROM Transactions AS T
INNER JOIN Product AS P
ON T.ProductID = P.ProductID
INNER JOIN Customer AS C
ON T.CustomerID = C.CustomerID
WHERE T.TranDate BETWEEN '2011-01-01' AND '2011-03-31'
GROUP BY
P.ProductName,
C.CustName
ORDER BY
P.ProductName
which gives the result like this:
这给出了这样的结果：
Product Customer Amount
Car Armstrong 80115.50
Car Michelle 36571.85
Car Schmidt 45000.65
Cycle Michelle 15000.00
Cycle Ronald 25000.00
Scooter Peterson 82658.23
Scooter Ronald 98547.52
Scooter Schmidt 54000.25
I need result of query in MATRIX form like this:
我需要这样的 MATRIX 形式的查询结果：
Customer |------------ Amounts ---------------
Name |Car Cycle Scooter Totals
Armstrong 80115.50 0.00 0.00 80115.50
Michelle 36571.85 15000.00 0.00 51571.85
Ronald 0.00 25000.00 98547.52 123547.52
Peterson 0.00 0.00 82658.23 82658.23
Schmidt 45000.65 0.00 54000.25 99000.90
Please help me to acheive the above result in SQL Server 2005. Using mulitple views or even temporory tables is fine for me.
请帮助我在 SQL Server 2005 中实现上述结果。使用多个视图甚至临时表对我来说都很好。
回答by Lieven Keersmaekers
You can use SQL Server's PIVOToperator
您可以使用 SQL Server 的PIVOT运算符
SELECT *
FROM (
SELECT P.ProductName
, C.CustName
, T.Amount
FROM Transactions AS T
INNER JOIN Product AS P ON T.ProductID = P.ProductID
INNER JOIN Customer AS C ON T.CustomerID = C.CustomerID
WHERE T.TranDate BETWEEN '2011-01-01' AND '2011-03-31'
) s
PIVOT (SUM(Amount) FOR ProductName IN ([Car], [Cycle], [Scooter])) pvt
Test data
测试数据
;WITH q AS (
SELECT [Product] = 'Car', [Customer] = 'Armstrong', [Amount] = 80115.50
UNION ALL SELECT 'Car', 'Michelle', 36571.85
UNION ALL SELECT 'Car', 'Schmidt', 45000.65
UNION ALL SELECT 'Cycle', 'Michelle', 15000.00
UNION ALL SELECT 'Cycle', 'Ronald', 25000.00
UNION ALL SELECT 'Scooter', 'Peterson', 82658.23
UNION ALL SELECT 'Scooter', 'Ronald', 98547.52
UNION ALL SELECT 'Scooter', 'Schmidt', 54000.25
)
SELECT Customer
, Car = ISNULL(Car, 0)
, Cycle = ISNULL(Cycle, 0)
, Scooter = ISNULL(Scooter, 0)
, Total = ISNULL(Car, 0) + ISNULL(Cycle, 0) + ISNULL(Scooter, 0)
FROM (
SELECT *
FROM q
) s
PIVOT (SUM(Amount) FOR Product IN ([Car], [Cycle], [Scooter])) pvt
Output
输出
Customer Car Cycle Scooter Total
Armstrong 80115.50 0.00 0.00 80115.50
Michelle 36571.85 15000.00 0.00 51571.85
Peterson 0.00 0.00 82658.23 82658.23
Ronald 0.00 25000.00 98547.52 123547.52
Schmidt 45000.65 0.00 54000.25 99000.90
回答by shashidhar reddy
We can create matrix using pivot, this can easily done with data frames
我们可以使用pivot创建矩阵，这可以通过数据框轻松完成
product|Key|Value
A |P |10|
A |Q |40|
B |R |50|
B |S |50|
val newdf=df.groupBy("product").pivot("key").sum("value")
|product|P |Q |R |S |
|B |null|null| 50| 50|
|A | 10| 40|null|null|
We can replace null and we can do calculations as well
我们可以替换 null 也可以进行计算
回答by Deena 786
create table #Product (ProductID int,ProductName varchar(15))
insert into #Product values (1,'Cycle')
insert into #Product values (2,'Scooter')
insert into #Product values (3,'Car')
create table #Customer (CustomerID int, CustomerName varchar(30))
insert into #Customer values (101,'Ronald')
insert into #Customer values (102,'Michelle')
insert into #Customer values (103,'Armstrong')
insert into #Customer values (104,'Schmidt')
insert into #Customer values (105,'Peterson')
create table #Transactions (TID int,ProductID int,CustomerID int, TranDate smalldatetime,Amount decimal(18,2))
insert into #Transactions values (10001,1,101,'01-Jan-11',25000.00)
insert into #Transactions values (10002,2,101,'02-Jan-11',98547.52)
insert into #Transactions values (10003,1,102,'03-Feb-11',15000.00)
insert into #Transactions values (10004,3,102,'07-Jan-11',36571.85)
insert into #Transactions values (10005,2,105,'09-Feb-11',82658.23)
insert into #Transactions values (10006,2,104,'10-Feb-11',54000.25)
insert into #Transactions values (10007,3,103,'20-Feb-11',80115.50)
insert into #Transactions values (10008,3,104,'22-Feb-11',45000.65)
with temp as
(
select cus.CustomerName,pro.ProductName, sum(trans.Amount) as Amount from #Transactions as trans
inner join #Customer as cus on trans.CustomerID = cus.CustomerID
inner join #Product as pro on trans.ProductID = pro.ProductID
group by cus.CustomerName,pro.ProductName
)
select CustomerName,isnull([Car],0)Car, isnull([Cycle],0)Cycle,isnull([Scooter],0) as Scooter, isnull([Car],0)+isnull([Cycle],0)+isnull([Scooter],0)as Total from temp
pivot (
sum(Amount) for ProductName in ([Cycle],[Scooter],[Car])
)pot*
