پیووت داینامیک روی چندین ستون در SQL Server

در این مقاله خواهیم دید که چطور میتوان یک pivot پویا روی چندین ستون ایجاد کرد.
برای این نسخه ی نمایشی، ما از پایگاه داده زیر استفاده میکنیم:  AdventureWorks2017
جدول: Sales.SalesOrderHeader

نیاز ما: ما باید یک پیووتی ایجاد کنیم که مقادیرSubtotal، TaxAmt، Freight، TotalDue (کرایه حمل، مبلغ مالیات، جمع جزء و دیون سررسید سالانه) را برای فروش کشورها نشان دهد. 

بیایید یک اسکریپت Dynamic SQL  برای این کار آماده کنیم.
اول از همه باید ستون داینامیک را برای Subtotal، TaxAmt، Freight و TotalDue ایجاد کنیم.

[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014]

[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014]

[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014]

[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014]

در اینجا ما لیست ستون ها را می دانیم. اکنون باید pivot را ایجاد کنیم.

DECLARE     @Sales_columns NVARCHAR(MAX) = ”;

DECLARE     @TaxAmt_columns NVARCHAR(MAX) = ”;

DECLARE     @Freight_columns NVARCHAR(MAX) = ”;

DECLARE     @TotalDue_columns NVARCHAR(MAX) = ”;

DECLARE     @sqlquery NVARCHAR(MAX) = ”;

DECLARE     @col NVARCHAR(MAX)=”;

DECLARE     @empty_col NVARCHAR(MAX)=”;

DECLARE     @Empty_Sales_columns NVARCHAR(MAX)=”;

DECLARE     @Empty_TaxAmt_columns NVARCHAR(MAX) = ”;

DECLARE     @Empty_Freight_columns NVARCHAR(MAX) = ”;

DECLARE     @Empty_TotalDue_columns NVARCHAR(MAX) = ”;

 

 

set          @Sales_columns=

(SELECT distinct ‘,’ +

     QUOTENAME(‘Sales_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @Sales_columns = Right(@Sales_columns, LEN(@Sales_columns) – 1);

 

 

set          @TaxAmt_columns=

(SELECT distinct ‘,’ +

     QUOTENAME(‘TaxAmt_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @TaxAmt_columns = Right(@TaxAmt_columns, LEN(@TaxAmt_columns) – 1);

 

set          @Freight_columns=

(SELECT distinct ‘,’ +

     QUOTENAME(‘Freight_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @Freight_columns = Right(@Freight_columns, LEN(@Freight_columns) – 1);

set          @TotalDue_columns=

(SELECT distinct ‘,’ +

     QUOTENAME(‘TotalDue_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @TotalDue_columns = Right(@TotalDue_columns, LEN(@TotalDue_columns) – 1);

set @col=@Sales_columns+‘,’+@TaxAmt_columns+‘,’+@Freight_columns+‘,’+@TotalDue_columns

set @col=replace(replace(@col,‘[‘,‘SUM(ISNULL([‘),‘]’,‘],0))’)

 

 

set          @Empty_Sales_columns=

(SELECT distinct ‘,’ +‘null as ‘+

     QUOTENAME(‘Sales_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @Empty_Sales_columns = Right(@Empty_Sales_columns, LEN(@Empty_Sales_columns) – 1);

 

 

set          @Empty_TaxAmt_columns=

(SELECT distinct ‘,’ +‘null as ‘+

     QUOTENAME(‘TaxAmt_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

 

SET @Empty_TaxAmt_columns = Right(@Empty_TaxAmt_columns, LEN(@Empty_TaxAmt_columns) – 1);

 

set          @Empty_Freight_columns=

(SELECT distinct ‘,’ +‘null as ‘+

     QUOTENAME(‘Freight_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @Empty_Freight_columns = Right(@Empty_Freight_columns, LEN(@Empty_Freight_columns) – 1);

set          @Empty_TotalDue_columns=

(SELECT distinct ‘,’ +‘null as ‘+

     QUOTENAME(‘TotalDue_’+”+cast(year(orderdate) as varchar(4)) )

FROM    sales.SalesOrderHeader s

order by 1 FOR XML PATH(”))

SET @Empty_TotalDue_columns = Right(@Empty_TotalDue_columns, LEN(@Empty_TotalDue_columns) – 1);

 

 

set @sqlquery=‘

Select

Name,

CountryRegionCode,

[Group],

‘+@Sales_columns+‘,’+@TaxAmt_columns+‘,’+@Freight_columns+‘,’+@TotalDue_columns+‘

from

(

select

null as Name,

null as CountryRegionCode,

null as [Group],

‘+@Empty_Sales_columns+‘,’+@Empty_TaxAmt_columns+‘,’+@Empty_Freight_columns+‘,’+@Empty_TotalDue_columns+‘

)x

where 1=2

union all

 

select Name,

CountryRegionCode,[Group],’+@col+‘ from (

select

Name,

CountryRegionCode,[Group],

‘+@Sales_columns+‘,’+@TaxAmt_columns+‘,’+@Freight_columns+‘,’+@TotalDue_columns+‘ from

(

Select

”Sales_”+cast(year(orderdate) as varchar(4)) as sales_Year

,”TaxAmt_”+cast(year(orderdate) as varchar(4)) as Tax_year

,”Freight_”+cast(year(orderdate) as varchar(4)) as Freight_year

,”TotalDue_”+cast(year(orderdate) as varchar(4)) as TotalDue_Year

,st.Name,st.CountryRegionCode,st.[Group]

,isnull(so.SubTotal,0) as Total_Sales

,isnull(so.TaxAmt,0) as Total_TaxAmt

,isnull(so.Freight,0) as Total_Freight

,isnull(so.TotalDue,0) as total_TotalDue

 FROM sales.SalesOrderHeader so

 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID

 

 )  as sales

 pivot

 (

  SUM(Total_Sales)

  FOR Sales_Year IN (‘+@Sales_columns+‘)

 )pvt_sales

 pivot

 (

  SUM(Total_TaxAmt)

  FOR Tax_year IN (‘+@TaxAmt_columns+‘)

 )pvt_Tax_year

 pivot

 (

  SUM(Total_Freight)

  FOR Freight_year IN (‘+@Freight_columns+‘)

 )pvt_Freight_year

  pivot

 (

  SUM(total_TotalDue)

  FOR TotalDue_Year IN (‘+@TotalDue_columns+‘)

 )pvt_TotalDue_Year)x

 group by Name,

CountryRegionCode,[Group]

 order by name;’;

 

  — execute the dynamic SQL

EXECUTE sp_executesql @sqlquery;

اجرای کوئری اسکریپت زیر را میسازد:

Select

Name,

CountryRegionCode,

[Group],

[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014],[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014],[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014],[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014]

from

(

select

null as Name,

null as CountryRegionCode,

null as [Group],

null as [Sales_2011],null as [Sales_2012],null as [Sales_2013],null as [Sales_2014],null as [TaxAmt_2011],null as [TaxAmt_2012],null as [TaxAmt_2013],null as [TaxAmt_2014],null as [Freight_2011],null as [Freight_2012],null as [Freight_2013],null as [Freight_2014],null as [TotalDue_2011],null as [TotalDue_2012],null as [TotalDue_2013],null as [TotalDue_2014]

)x

where 1=2

union all

 

select Name,

CountryRegionCode,[Group],SUM(ISNULL([Sales_2011],0)),SUM(ISNULL([Sales_2012],0)),SUM(ISNULL([Sales_2013],0)),SUM(ISNULL([Sales_2014],0)),SUM(ISNULL([TaxAmt_2011],0)),SUM(ISNULL([TaxAmt_2012],0)),SUM(ISNULL([TaxAmt_2013],0)),SUM(ISNULL([TaxAmt_2014],0)),SUM(ISNULL([Freight_2011],0)),SUM(ISNULL([Freight_2012],0)),SUM(ISNULL([Freight_2013],0)),SUM(ISNULL([Freight_2014],0)),SUM(ISNULL([TotalDue_2011],0)),SUM(ISNULL([TotalDue_2012],0)),SUM(ISNULL([TotalDue_2013],0)),SUM(ISNULL([TotalDue_2014],0)) from (

select

Name,

CountryRegionCode,[Group],

[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014],[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014],[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014],[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014] from

(

Select

‘Sales_’+cast(year(orderdate) as varchar(4)) as sales_Year

,‘TaxAmt_’+cast(year(orderdate) as varchar(4)) as Tax_year

,‘Freight_’+cast(year(orderdate) as varchar(4)) as Freight_year

,‘TotalDue_’+cast(year(orderdate) as varchar(4)) as TotalDue_Year

,st.Name,st.CountryRegionCode,st.[Group]

,isnull(so.SubTotal,0) as Total_Sales

,isnull(so.TaxAmt,0) as Total_TaxAmt

,isnull(so.Freight,0) as Total_Freight

,isnull(so.TotalDue,0) as total_TotalDue

 FROM sales.SalesOrderHeader so

 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID

 

 )  as sales

 pivot

 (

  SUM(Total_Sales)

  FOR Sales_Year IN ([Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014])

 )pvt_sales

 pivot

 (

  SUM(Total_TaxAmt)

  FOR Tax_year IN ([TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014])

 )pvt_Tax_year

 pivot

 (

  SUM(Total_Freight)

  FOR Freight_year IN ([Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014])

 )pvt_Freight_year

  pivot

 (

  SUM(total_TotalDue)

  FOR TotalDue_Year IN ([TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014])

 )pvt_TotalDue_Year)x

 group by Name,

CountryRegionCode,[Group]

 order by name;

 

با اجرای اسکریپت بالا نتیجه موردنظر را خواهیم داشت: