SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
52325 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
52325 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  
 New 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.14 seconds. Powered By: Snitz Forums 2000