Solved

Create a dynamic view dependign on a certian

Posted on 2013-01-25
2
151 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard 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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

18 Experts available now in Live!

Get 1:1 Help Now