Solved

Create a dynamic view dependign on a certian

Posted on 2013-01-25
2
155 Views
Last Modified: 2013-03-12
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
Comment
Question by:Sonali Paradkar
2 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38822159
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38823483
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 58
Why I am having this error in my query? 2 32
TSQL recursive CTE challenge... 8 33
shrink table after huge delete 2 27
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question