具有多列的 SQL Server 动态数据透视表 [英] SQL Server dynamic pivot with multiple columns

问题描述

这是我所处的场景.我的数据格式如下.

Here's the scenario I am in. I have my data in the following format.

我的源数据

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

这是我正在寻找的输出

预期输出

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

推荐答案

以下是用于我的大量动态数据透视表的存储过程

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

存储过程

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)

这篇关于具有多列的 SQL Server 动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！