Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting a column in a view
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andypgill
Starting Member

34 Posts

Posted - 12/07/2012 :  10:48:47  Show Profile  Reply with Quote
Hi

I have created a view "Andy" which is linked to excel. The user in excel inputs a month.

Is it possible to select a particular column in the view based on the month.

eg If month - 7 select col 20 in the view, if month = 8 select col 21.

Thanks


Is it possible to

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/07/2012 :  10:51:29  Show Profile  Reply with Quote
are the col 20,col 21 etc in same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/07/2012 :  10:54:24  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Why not make it a stored procedure and pass the month to it.
Otherwise the view could have two columns, one with the month number (monthno) and the other with the data - concatenate (union all) for all months.
then select * from myview where monthno = mymonthno

If there are a lot of other columns then put these two in another table and join to it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nigelrivett on 12/07/2012 10:55:16
Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/07/2012 :  10:55:11  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

are the col 20,col 21 etc in same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Yes the view Andy selects the columns concerned from table Budget. The view also has columns from other tables

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/07/2012 :  10:59:12  Show Profile  Reply with Quote
Thinking about it the actual column names are M1,M2,M3 etc.

So if the user selects month 7 could I get it to use column M7

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/07/2012 :  11:02:24  Show Profile  Reply with Quote
do you mean view has to be dynamic based on user selection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/07/2012 :  11:17:12  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
You can put another view on top of your existing one
create view Andy2
as
select col1, ..., monthno=1, M = M1 from Andy
union all
select col1, ..., monthno=2, M2 from Andy
union all
...

then
select Col1, ..., M from Andy2 where Monthno = mymonthno



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/10/2012 :  04:33:25  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

do you mean view has to be dynamic based on user selection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Yes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/14/2012 :  02:06:19  Show Profile  Reply with Quote
quote:
Originally posted by andypgill

quote:
Originally posted by visakh16

do you mean view has to be dynamic based on user selection?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Views cant be parameter driven. If thats your requirement you may be better off creating a table valued UDF or procedure instead where resultset varies as per user supplied parameter

Yes



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000