Pivoting with Sum function in tsql

问题:

I have data in following format

Client       Business Unit    Year  Quarter     USD Amt
BalckRock    Pricing          2010  Q1          234
BalckRock    Pricing          2010  Q2          343
BalckRock    Pricing          2010  Q3          545
BalckRock    Pricing          2010  Q4          5435
BalckRock    Pricing          2011  Q1          5425
BalckRock    Pricing          2011  Q2          3524
BalckRock    Pricing          2011  Q3          54
BalckRock    Pricing          2011  Q4          5425
BalckRock    Pricing          2012  Q1          545
BalckRock    Pricing          2012  Q2          5445
BalckRock    Pricing          2012  Q3          545
BalckRock    Pricing          2012  Q4          4545
BalckRock    Sales            2010  Q1          23
BalckRock    Sales            2010  Q2          3434
BalckRock    Sales            2010  Q3          4234
BalckRock    Sales            2010  Q4          4234
BalckRock    Sales            2011  Q1          3423
BalckRock    Sales            2011  Q2          1
BalckRock    Sales            2011  Q3          1341
BalckRock    Sales            2011  Q4          434
BalckRock    Sales            2012  Q1          421
BalckRock    Sales            2012  Q2          42
BalckRock    Sales            2012  Q3          434
BalckRock    Sales            2012  Q4          4214

And I want it in following format

Client        Business Unit    2010    2011    2012
BalckRock     Pricing          6557    14428   11080
BalckRock     Sales            11925   5199    5111

Basically the sum of USD amnt year wise, but years as the column heading

Can anyone help me in this ?

回答1:

There are different ways, you can use PIVOT (static or dynamic depending on your needs) or you can simply use a CASE:

SELECT  Client,
        [Business Unit],
        SUM(CASE WHEN [Year] = 2010 THEN [USD Amt] ELSE 0 END) [2010],
        SUM(CASE WHEN [Year] = 2011 THEN [USD Amt] ELSE 0 END) [2011],
        SUM(CASE WHEN [Year] = 2012 THEN [USD Amt] ELSE 0 END) [2012]
FROM YourTable
GROUP BY Client, [Business Unit]

Using PIVOT:

SELECT *
FROM (  SELECT Client, [Business Unit], [USD Amt], [Year]
        FROM YourTable) T
PIVOT (SUM([USD Amt]) FOR [Year] IN ([2010],[2011],[2012])) PT

回答2:

You can easily use a PIVOT for this. As the other have said you can use a Static Pivot that you code the columns that you want or you can use a Dynamic Pivot, which get the list of columns at run-time.

Static Pivot (See Sql Fiddle for Demo)

select *
from
(
  select client, businessunit, year, USD_Amount
  from t
) x
pivot
(
  sum(USD_Amount)
  for year in ([2010], [2011], [2012])
) p

But for this, I might recommend a dynamic Pivot so you don't have to change you code when you get into a new year. (See Sql Fiddle with Demo)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)


select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year) 
                    from t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT client, businessunit, ' + @cols + ' from 
             (
                select client, businessunit, year, USD_Amount
                from t
            ) x
            pivot 
            (
                sum(USD_Amount)
                for year in (' + @cols + ')
            ) p '

execute(@query)

Both queries will produce the same results. However, the plus with the dynamic pivot is that when you have data from additional years, you will not have to update the query to include those fields. The first part of the query gets the list of the distinct years in your table, it then uses that list of years to determine the SUM that you are looking for.

回答3:

-- Pivot table with one row and five columns

SELECT Client  Business Unit   Year    Quarter USD Amt,[2010],[2011],[2012]
FROM
(SELECTClient  Business Unit   Year    Quarter USD Amt 
    FROM Table) AS SourceTable
PIVOT
(
sum(USD Amt)
FOR Year IN ([2010], [2011], [2012])
) AS PivotTable;