Stored procedure that adds columns to view dynamically?

Question

We currently have an unpivoted table like so:

Sample Data in text format:

Period Scenario Styling Accounts Vals
12+0 Scen style1 Volume 0.0987
1+11 Scen1 style2 Revenue 1.8765
1+11 Scen style3 Sales -9876
2+10 Scen2 style2 Net Sales 4567

By unpivoted i mean, the Accounts are rows, not columns.

Due to a reporting slicer requirement users have asked for, we had to create a pivoted view of this table and then unpivot the pivoted view in order to transform the accounts back to rows.

Note: The slicer requirement details are outside the scope of this question, so ill leave out these irrelevant details but wanted to mention this information in case you ask why we are doing this when the original table is already unpivoted.

So, from time to time, a new account is added. This means that I have to add this new account to the pivoted and unpivoted views sql and recreate it.

Here is example pivoted view DDL;

CREATE VIEW [dbo].[Pivoted_Fact]  
AS  
(SELECT  
  [Period],  
  [Scenario],  
  [Styling],  
  --[Country_Style_Code],  
  COALESCE([Volume], 0) [Volume], --Convert NULLs to 0's  
  COALESCE([Revenue], 0) [Revenue],  
  COALESCE([Sales], 0) [Sales],  
  COALESCE([Net Sales], 0) [Net Sales]  
FROM Fact_MeasuresUnpivoted   
 PIVOT (SUM([Vals]) FOR [Accounts] IN (  
 [Volume],   
 [Revenue],   
 [Sales],   
 [Net Sales]  
 )  
 )   
AS Pivoted_Fact);  

Is there a stored procedure that can dynamically add the additional account to these views? The idea is that, we will have a table called Accounts, and the user can just add the new account to this Accounts table, and then a stored procedure will detect this newly added account and add it to the pivoted/unpivoted view DDL automatically.

Is this possible? if so, how?

Answer 1

Something like this:

Declare @factTable Table (Account varchar(20));
 Insert Into @factTable (Account)
 Values ('Account1'), ('Account2'), ('Account3');

    Set Nocount On;

Declare @sqlCommand nvarchar(max) = ''
      , @columnList nvarchar(max) = ''
      , @pivotColumns nvarchar(max) = '';

 Select @columnList = (Select Distinct concat(', ', quotename(ft.Account), ' = ', 'coalesce(', quotename(ft.Account), ', 0)')
                               From @factTable ft
                                For xml Path(''), Type).value('.', 'nvarchar(max)');

 Select @pivotColumns = stuff((Select Distinct concat(', ', quotename(ft.Account))
                                 From @factTable ft
                                  For xml Path(''), Type).value('.', 'nvarchar(max)'), 1, 2, '');

    Set @sqlCommand = '
    Use SomeDatabase;
     Go

   Drop View If Exists dbo.Pivoted_Fact;
     Go

 Create View dbo.Pivoted_Fact
     As

 Select ft.Period
      , ft.Scenario
      , Styling
      --, Country_Style_Code
      , ' + @columnList + '
   From @factTable              ft 
  Pivot (sum([Vals]) FOR [Accounts] IN (' + @pivotColumns + ')) As pf;';

 Print @sqlCommand;
--Execute sp_executesql @sqlCommand;

Answer 2

Yes, that can be done with help of dynamic SQL, but keep in mind that dynamic SQL is an advanced feature. As a starting point, you need to learn to master dynamic pivot, and I have some text here:
http://www.sommarskog.se/dynamic_sql.html#pivot

But if you are not experienced with dynamic SQL, you may be better by starting from the beginning.

Answer 3

Hi @Cataster ,

I watched the entire thread and the experts provided a lot of suggestions. Has your problem been solved?If your problem has been solved, please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo