CODEWITHSUNDEEP
sql-serverinsertpivot-table
Fareedah Mahmood
Fareedah Mahmood

Reputation: 13

Dynamic pivot table insert

I have created a dynamic pivot table by concatenating column names and executing a the string. The table results look like this

   ProductID    A       B      C     D

In future an additional field may be entered (E. F... Ect) hence the need for the piviot query to be dynamic. I need to insert this data into an existing table which has columns A to Z.

How can I create the insert query to be dynamic so that when a new field is added then the insert query doesn't need to be changed in the code?

Upvotes: 1

Views: 150

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

A stored proc (listed below) will pivot data with just a few options. You can have multiple group by's. The following will take the results from a #temp table and pivot based on the parameters. I should note. the columns can be an expressions.

Select Year=Year(TR_Date),Day=Day(TR_Date),Month=right(concat('00',Month(TR_Date)),2),TR_Y10 Into #Temp from  [Chinrus-Series].[dbo].[DS_Treasury_Rates] where TR_Date>='2000-01-01'

Exec [prc-Pivot] 'Select * from #Temp','Month','sum(TR_Y10)[]','Year,Day'

Returns

Year    Day 01      02      03      04      05      06      07      08      09      10      11      12
2000    1   0.00    6.62    6.39    0.00    6.29    6.20    0.00    6.00    5.68    0.00    5.74    5.52
2000    2   0.00    6.60    6.40    0.00    6.32    6.15    0.00    5.98    0.00    5.83    5.74    0.00
2000    3   6.58    6.49    6.39    6.00    6.40    0.00    6.00    5.95    0.00    5.87    5.83    0.00
2000    4   6.49    6.53    0.00    5.90    6.46    0.00    0.00    5.91    0.00    5.90    0.00    5.53
2000    5   6.62    0.00    0.00    5.90    6.51    6.12    5.99    0.00    5.69    5.87    0.00    5.43
2000    6   6.57    0.00    6.42    5.93    0.00    6.14    6.05    0.00    5.72    5.82    5.87    5.32
2000    7   6.52    6.64    6.39    5.86    0.00    6.13    6.01    5.97    5.76    0.00    5.87    5.32
2000    8   0.00    6.59    6.38    0.00    6.57    6.13    0.00    5.93    5.73    0.00    5.87    5.35
2000    9   0.00    6.56    6.35    0.00    6.53    6.13    0.00    5.81    0.00    0.00    5.82    0.00
2000    10  6.57    6.67    6.39    5.80    6.47    0.00    6.04    5.76    0.00    5.80    5.82    0.00
2000    11  6.67    6.63    0.00    5.89    6.43    0.00    6.06    5.79    5.77    5.77    0.00    5.37
2000    12  6.72    0.00    0.00    5.97    6.51    6.09    6.09    0.00    5.78    5.73    0.00    5.36
2000    13  6.63    0.00    6.36    5.94    0.00    6.11    6.01    0.00    5.74    5.73    5.77    5.29

... (527 rows in total)

I should add, If your destination table has a fixed structure, you can

INSERT YourTableName  Exec [prc-Pivot] 'Select * from #Temp','Month','sum(TR_Y10)[-OptionalSuffix]','Year,Day'

The stored procedure: 

CREATE PROCEDURE [dbo].[prc-Pivot] (@Select varchar(1000),
    @PvotCol varchar(100),
    @Summaries varchar(100),
    @GroupBy varchar(100),
    @OtherCols varchar(100) = Null)
AS

Set NoCount On
Set Ansi_Warnings Off

Declare @Vals varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Select + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Exec ('Select ' + @GroupBy + @OtherCols + @Vals + ' From (' + @Select + ') A Group By ' + @GroupBy + ' Order by ' + @GroupBy)

Set NoCount Off
Set Ansi_Warnings on

Some extended Options:

Exec [prc-Pivot] 'Select * from #Temp','Month','avg(TR_Y10)[-Month]','Year','min(TR_Y10)[Min],max(TR_Y10)[Max],avg(TR_Y10)[Avg],cast(stdev(TR_Y10) as money)[StdDev]'


Year    Min Max Avg StdDev  01-Month    02-Month    03-Month    04-Month    05-Month    06-Month    07-Month    08-Month    09-Month    10-Month    11-Month    12-Month
2000    5.02    6.79    6.0302  0.3919  6.661   6.5195  6.2565  5.9905  6.4404  6.0972  6.054   5.826   5.799   5.7385  5.7171  5.2405
2001    4.22    5.54    5.0206  0.2857  5.1609  5.0989  4.8854  5.141   5.3913  5.2842  5.2361  4.9713  4.7317  4.5668  4.6515  5.0875

Upvotes: 1

Related Questions