SQL Server dynamic pivot with multiple columns

Question

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

Answer 1

The following is the stored procedure used for the bulk of my dynamic pivots

Exec [prc-Pivot] 'tbl1','Country','sum(Sales)[]','IssuedOn','sum(Transactions)[Transactions],sum(Sales)[TotalSales]'


IssuedOn    Transactions    TotalSales  Australia   Canada  India
2016-08-29  14              70          15          15      40
2016-08-30  17              85          25          10      50
2016-08-31  47              185         30          55      100

The stored procedure

ALTER PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'
--Exec [prc-Pivot] '#Temp','Attribute','max(Description)[]','ID','count(*)[Records]'

Set NoCount On

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
--Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),NULL) As [' + Pvot ) From #TempPvot Order by Pvot
  Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)

Answer 2

here's a simple way to do it using aggregate case expressions.

DECLARE @cols NVARCHAR(MAX),
        @sql NVARCHAR(MAX)

SELECT @cols = STUFF((
        SELECT ',' + 'SUM(CASE WHEN Country = ''' + Country + ''' THEN Sales END) AS ' + QUOTENAME(Country)
        FROM (SELECT DISTINCT Country FROM tbl1) t
        ORDER BY Country
        FOR XML PATH('')
    ),1, 1, '')

SET @sql = N'
    SELECT  IssuedOn, ' + @cols + ',
            SUM(Sales) AS TotalSales,
            SUM(Transactions) AS TransactionCount
    FROM    tbl1 
    GROUP BY IssuedOn
'
EXEC(@sql)

this will generate a query that should look like this.

SELECT  IssuedOn, 
        SUM(CASE WHEN Country = 'Australia' THEN Sales END) AS [Australia],
        SUM(CASE WHEN Country = 'Canada' THEN Sales END) AS [Canada],
        SUM(CASE WHEN Country = 'India' THEN Sales END) AS [India],
        SUM(Sales) AS TotalSales,
        SUM(Transactions) AS TransactionCount
FROM    tbl1 
GROUP BY IssuedOn

Answer 3

A more verbose dynamic SQL query, without the use of a stored procedure is as follows:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @pivotSales AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);

SELECT @cols = ISNULL(@cols + ', ', '') + QUOTENAME(Country)
FROM (SELECT DISTINCT Country FROM tbl1) AS Countries

SET @pivotSales = N'SELECT IssuedOn, ' + @cols +'
FROM (SELECT IssuedOn, Country, Sales FROM tbl1) AS sales
PIVOT(SUM(Sales) FOR Country IN (' + @cols + ')) AS pvt';

SET @sql = ';WITH CTE_SalesPivot AS (
'+@pivotSales+'
),
CTE_SalesTotal AS (
  SELECT IssuedOn, SUM(Sales) AS [Grand Total]
  FROM tbl1
  GROUP BY IssuedOn
),
CTE_Transactions AS (
  SELECT IssuedOn, SUM(Transactions) AS [Transaction Count]
  FROM tbl1
  GROUP BY IssuedOn
)
SELECT CTE_SalesPivot.IssuedOn, ' + @cols + ', CTE_SalesTotal.[Grand Total], CTE_Transactions.[Transaction Count]
FROM
CTE_SalesPivot
INNER JOIN CTE_SalesTotal ON CTE_SalesPivot.IssuedOn = CTE_SalesTotal.IssuedOn
INNER JOIN CTE_Transactions ON CTE_SalesPivot.IssuedOn = CTE_Transactions.IssuedOn';

EXEC sp_executesql @sql;