SQL Query to generate a matrix, such as an output-related query table in SQL Server

Question

I have three tables:
Product

ProductID ProductName 1 Cycle 2 Scooter 3 Car 

Client

 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 wrote a query to group transactions as follows:

 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 as follows:

 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 the query result in a MATRIX form as follows:

 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 achieve the above result in SQL Server 2005. Using mulitple views or even temporary tables is suitable for me.

Answer 1

You can use 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 

Exit

 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 

Answer 2

We can create a matrix using Pivot, this is easy to do with data frames.

 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 zero, and we can do calculations

Answer 3

 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*