Stored procedure that adds columns to view dynamically?

Cataster 641 Reputation points
2021-01-20T22:02:24.027+00:00

We currently have an unpivoted table like so:

table

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?

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
Transact-SQL
Transact-SQL
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
    2021-01-21T21:25:34.287+00:00

    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;
    

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points MVP
    2021-01-20T22:23:37.687+00:00

    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.


  2. EchoLiu-MSFT 14,571 Reputation points
    2021-01-25T07:06:56.567+00:00

    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

    0 comments No comments