Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create a dynamic view dependign on a certian

Posted on 2013-01-25
2
Medium Priority
?
159 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 P
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 1500 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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 response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 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