• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Create a dynamic view dependign on a certian

CREATE TABLE [dbo].[table1](
                  [sk] INTEGER NOT NULL,
                  [id] INTEGER NOT NULL,
                  [condition] NVARCHAR(80) NOT NULL,
                  [value] NVARCHAR(150) NOT NULL,
                  [isNumeric] BIT NOT NULL,
                  CONSTRAINT [PK_table1__sk] PRIMARY KEY CLUSTERED (
                        [sk] ASC
                  ) WITH (
                        PAD_INDEX  = OFF,
                        STATISTICS_NORECOMPUTE  = OFF,
                        IGNORE_DUP_KEY = OFF,
                        ALLOW_ROW_LOCKS  = ON,
                        ALLOW_PAGE_LOCKS  = ON,
                        FILLFACTOR = 90
                  ) ON [PRIMARY]
            ) ON [PRIMARY]

Using the table above, we would like to build a dynamic SQL to construct a view called table1_pivoted.

Problem is, I was doing research and it seems that it’s impossible to use dynamic SQL in a view definition (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/87bdee6d-be2d-497b-ae76-334d79908aca).

Any advise how this can be done.
0
SP_2018 .
Asked:
SP_2018 .
1 Solution
 
Mark WillsTopic AdvisorCommented:
Yeah, cannot use dynamic SQL in a view, or a function for that matter...

But you could use a stored procedure and exec the procedure. Havent really investigated using CLR procedures...

The big problem with pivot is needing to know the new column names.

There are some convoluted tricks around where you can generate an EAV type model and use generic column names (like col1, col2 etc) and hard code the 'known' column names - problem with that is you need to include as many columns as there are possible different values.

I have also seem some rather involved code (dynamic SQL) to create the view with the known columns, and a trigger would check the addition of a new column value.

I have also seen openrowset to emulate a select except it is from a Stored Procedure as if it were a function...

The biggest challenge with all of that is selecting from the new datasource when the columns are not always known.

Interesting problem. Care to share why it needs to be a view instead of say a Stored Procedure ?

I have written a couple of Article about PIVOT and might be of interest :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html
0
 
David ToddSenior DBACommented:
Hi,

I just commented on another question here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28009507.html

What I'm suggesting is to let the data describe itself - current, last week, the week before ... and train your users to read the slightly different to (initial expectations) report format.

Sometimes its easier to fix the users - unless they are a C level user <grin>

HTH
  David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now