Pivot more than one column per key value

Transact-SQL
1

Hello,
I have a sales table, simplified as:
ProductId int
SaleDate date
Quantity dec(12,3)
wich I managed to pivot so we have Sum(Quantity) for products with one column/day.
Is there a possibility to pivot two (or more) aggregate columns? Like:
ProductId int
SaleDate date
Quantity decimal(12,3)
SaleAmount decimal(10,2)
I would like to have one row/product and for each day a pair of columns: Sum(Quantity) and Sum(SaleAmont).

Thank you,
Daniel

2

No sample data, so I wasn't sure if SaleDate included a time; I assumed it did, so I stripped the time in the code below.

SELECT
    ProductId,
    DATEADD(DAY, DATEDIFF(DAY, 0,SaleDate), SaleDate) AS SaleDay,
    SUM(Quantity) AS Quantity,
    SUM(Quantity*Price) AS SaleAmount
FROM dbo.table_name
GROUP BY ProductId, DATEADD(DAY, DATEDIFF(DAY, 0,SaleDate), SaleDate)
ORDER BY ProductId, SaleDay
3

Thank you @ScottPletcher
I think I didn't explain the problem well: I need to PIVOT the values, so I will get a column for EACH day, and the column should contain Sum(Quantity).
The questions is if I can have a pair of aggregated columns (sum qty and sum amount) for each day.

4

Hi,
I've tried some queries and I think I found a solution:

The query can have more pivoted subqueries and thus have more columns for the same aggregation condition.
For the sake of example let's assume the totals will be on the day of the month, not on the date, and I will have something like:

Select Products.Description,
TQ.[1] as colTQ1, TA.[1] as colTA1, TQ.[2] as colTQ2, TA.[2] as colTA2,<... and so on>
From (Select ProductId, Day(SaleDate),Quantity From
(Select ProductId, Day(SaleDate),Sum(Quantity) As Quantity From dbo.Sales
Group By ProductId,Day(SaleDate)) As pa) As Source
Pivot (Sum(Quantity) For Day(SaleDate) In ([1],[2],[3],...)) As TQ
Inner Join (Select ProductId, Day(SaleDate),Amount From
(Select ProductId, Day(SaleDate),Sum(Amount) As Amount From dbo.Sales
Group By ProductId,Day(SaleDate)) As pa) As Source
Pivot (Sum(Amount) For Day(SaleDate) In ([1],[2],[3],...)) As TA
On TQ.ProductId=TA.ProductId
Inner Join Products on Products.Id = TQ.ProductId

The column list is to be dynamically generated into local variables prior to this query.
Thus I managed to have a pair of aggregated values (sum(quantity) and sum(amount)) pivoted for each day.

Daniel

5

You don't have to PIVOT, you can use a CROSS TAB instead. But without sample data and sample results, it's impossible for us to know the results you want.

1 Like