Here's the scenario I am in. I have my data in the following format.
My source data
IssuedOn Country Sales Transactions
------------------------------------------
29-Aug-16 India 40 8
29-Aug-16 Australia 15 3
29-Aug-16 Canada 15 3
30-Aug-16 India 50 10
30-Aug-16 Australia 25 5
30-Aug-16 Canada 10 2
31-Aug-16 India 100 25
31-Aug-16 Australia 30 10
31-Aug-16 Canada 55 12
This is the output I am looking for
Expected output
IssuedDate Australia Canada India TotalSales Transactionscount
---------------------------------------------------------------------
29-Aug-16 15 15 40 70 14
30-Aug-16 25 10 50 85 17
31-Aug-16 30 55 100 185 47
I have been able to pivot the data on country and get the "Total Sales" column. However, I am not able to get the "Total Transactions" column right.
Here's the code to generate the source data table. Would really help if someone can guide me.
Create Table tbl1
(
IssuedOn date,
Country varchar(100),
Sales bigint,
Transactions bigint
)
Insert into tbl1(IssuedOn, Country, Sales, Transactions)
Values ('2016-08-29', 'India', 40, 8),
('2016-08-29', 'Australia', 15, 3),
('2016-08-29', 'Canada', 15, 3),
('2016-08-30', 'India', 50, 10),
('2016-08-30', 'Australia', 25, 5),
('2016-08-30', 'Canada', 10, 2),
('2016-08-31', 'India', 100, 25),
('2016-08-31', 'Australia', 30, 10),
('2016-08-31', 'Canada', 55, 12)
select *
from tbl1
question from:
https://stackoverflow.com/questions/65908211/sql-server-dynamic-pivot-table-add-average-column