Dynamically and conditionally adding columns to view

Question

I have the following view in SQL Server 2014:

CREATE VIEW sales_snapshot 
AS
    SELECT
        sdb.sku,
        sdb.district,
        sdb.num_units,
        sdb.location
    FROM 
        sales_db as sdb
GO

What I would like to do, is add additional columns, where the column name is location_stock and the value would be sdb.num_units.

For instance, if location = 'headquarters' then the column name headquarters_stock would be added to the view, and the value would be sdb.num_units. This would be if the column does not exist.

Edit: if this cannot be done dynamically, and I add the column names:

headquarters
new_york
ohio

How would I fill the right column with data, based on the value of sdb.location?

Any idea how to do this?

Just permanently add the columns but only select them when you need them. There is no way to dynamically add/remove columns. — Dale K, Jun 08 '20 at 23:04
Please see edit. How would I do this conditionally as part of the select statement? — EastsideDev, Jun 08 '20 at 23:12
But why do you need a different column per location? This is an app/client code problem. Just change the header in your display code based on the location provided? — Dale K, Jun 08 '20 at 23:15
But if you really have to do it that way, just use `case when location = '' then stock else 0 end` and change the location for each column. — Dale K, Jun 08 '20 at 23:16
What is your version of SQL Server? There is no such thing as SQL Server 2015. — Alex, Jun 09 '20 at 00:01
You can try creating a table valued function with a IF condition. Have 2 sets of Select based on if condition is true or false. — VTi, Jun 09 '20 at 00:11

Alex · Accepted Answer · 2020-06-09T00:29:57.810

There is no way to dynamically add columns to a view, even if there was it would cause problems down the line for queries using said views due to references to missing columns etc.

If you have a fixed number of locations known in advance, then you can use PIVOT:

CREATE VIEW sales_snapshot AS
    -- If you want column names changed e.g headquarters_stock, then you should do it here
    SELECT sdb.sku, db.district, [headquarters] AS [headquarters_stock], [new_york], [ohio]
    FROM
        -- If you have an empty (or NULL) location, you would need to assign
        -- a default non empty value to it e.g.
        -- ISNULL( NULLIF( sdb.location, '' ), 'empty' ) AS [location]
        ( SELECT sdb.sku, sdb.district, sdb.num_units, sdb.location
        FROM sales_db as sdb ) AS SourceT
    PIVOT(
        SUM( num_units ) FOR [location] IN( [headquarters], [new_york], [ohio] )) AS PivotT;

If locations change dynamically then you would need to implement a dynamic PIVOT. As views cannot have dynamic SQL in them, you would need to add logic to dynamically re-create/alter view when a new location is added / removed. Unless you have a very good reason for it and you know what you are doing, I would not recommend using a view in this scenario.

This got me in the right direction. Normally I do this via application code not SQL code. — EastsideDev, Jun 09 '20 at 02:26

prab · Answer 2 · 2020-06-09T03:58:23.607

You cannot add a dynamic columns in a view.But to answer the "Edit Part" of your question, you can use a case statement in your view.

CREATE VIEW sales_snapshot AS
SELECT
    sdb.sku,
    sdb.district,
    sdb.num_units,
    sdb.location,
    CASE WHEN sdb.location='headquarters' THEN sdb.num_units ELSE NULL END as headquarters_stock
FROM sales_db as sdb
GO

Syntax for a CASE with multiple conditions goes like this

CASE 
WHEN alphabet='a' THEN 'Amy'
WHEN alphabet='b' THEN 'Boyd'
ELSE "Zia"
END as "new_column_name"

Dynamically and conditionally adding columns to view

2 Answers2