Selecting a column in a view - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting a column in a view

Author

Topic

andypgill
Starting Member

34 Posts

Posted - 2012-12-07 : 10:48:47

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

52326 Posts

Posted - 2012-12-07 : 10:51:29

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

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 10:54:24

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.

andypgill
Starting Member

34 Posts

Posted - 2012-12-07 : 10:55:11

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

andypgill
Starting Member

34 Posts

Posted - 2012-12-07 : 10:59:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-07 : 11:02:24

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

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 11:17:12

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.

andypgill
Starting Member

34 Posts

Posted - 2012-12-10 : 04:33:25

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 02:06:19

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources