Solved

Create a dynamic view dependign on a certian

Posted on 2013-01-25
2
152 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:isonali
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now