SQL Query for generating matrix like output querying related table in SQL Server

Question

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:

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.

Lieven Keersmaekers · Accepted Answer · 2011-03-07T08:05:04.520

You can use SQL Server's PIVOT operator

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

thank you very much for the solution. My product list is not static (user can add products). As the products grow, that many columns have to be created. Also the data is dynamic. Please modify the query so that I can query the dynamic data. — Nagesh, Mar 07 '11 at 09:20
@Nagesh, you can find different implementations of dynamic pivotting at our very own SO itself. Have a look at http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns — Lieven Keersmaekers, Mar 07 '11 at 09:25

score 2 · Answer 2 · edited Aug 27 '19 at 06:10

2

We can create matrix using pivot, this can easily done 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 null and we can do calculations as well

edited Aug 27 '19 at 06:10

Obsidian

3,719
8
17
30

answered Aug 26 '19 at 19:39

shashidhar reddy

31
2

score 1 · Answer 3 · edited Oct 13 '22 at 05:17

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) as Car
  , isnull([Cycle],0) as 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])
) as pot

SQL Query for generating matrix like output querying related table in SQL Server

3 Answers3

Linked