SQL-Abfrage für die Generierung von matrix-Ausgang Abfragen von verknüpften Tabelle in SQL Server

Ich habe drei Tabellen:

Produkt

ProductID   ProductName  
1           Cycle  
2           Scooter  
3           Car

Kunden

CustomerID  CustomerName  
101         Ronald  
102         Michelle  
103         Armstrong  
104         Schmidt  
105         Peterson

Transaktionen

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

Ich geschrieben habe, eine Abfrage zum gruppieren von Transaktionen wie diese:

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

gibt das Ergebnis wie dieses:

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

Ich brauche das Ergebnis der Abfrage in MATRIX-form wie diese:

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

Bitte helfen Sie mir, um acheive dem obigen Ergebnis in SQL Server 2005. Über mehrere Ansichten oder sogar temporory Tabellen ist in Ordnung für mich.

InformationsquelleAutor Nagesh | 2011-03-07

Können Sie SQL Server PIVOT Betreiber

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

Testdaten

;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

Ausgabe

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

vielen Dank für die Lösung. Mein Produkt Liste ist nicht statisch (Benutzer können Produkte hinzufügen). Wie die Produkte wachsen, die viele Spalten erstellt werden. Auch die Daten, die dynamisch ist. Bitte ändern Sie die Abfrage so, dass ich die Abfrage der dynamischen Daten.
Sie können verschiedene Implementierungen von dynamischen pivotting in unserer eigenen, ALSO sich selbst. Haben Sie einen Blick auf stackoverflow.com/questions/213702/...
ist es möglich mit MySQL

InformationsquelleAutor Lieven Keersmaekers

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*

InformationsquelleAutor Deena 786

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.