Stored procedure that adds columns to view dynamically?

Cataster 641 Reputation points

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]  
  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 (  
 [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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,853 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jeffrey Williams 1,891 Reputation points

    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;
       Drop View If Exists dbo.Pivoted_Fact;
     Create View dbo.Pivoted_Fact
     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;

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points MVP

    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:

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

  2. EchoLiu-MSFT 14,571 Reputation points

    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.


    0 comments No comments