Views - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Views

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-26 : 07:51:23

Hannes Gouws writes "Hi,

I am trying to create a view with a "virtual" column.

The scenario:
I have a table "Table1" with 3 columns called "ID", "From" and "To" respectively. My From col has a value of 10 and my To col a value of say 15. I want to create a view that will combine this two separate colums into one "virtual" column and give me the full list of:

ID Value
1 10
2 11
3 12
4 13
5 14
6 15

instead of:

ID From To
1 10 15

How will I go about doing this trick as I know that I'm a bit limited with the view.

Kind regards,
Hannes"

dsdeming

479 Posts

Posted - 2003-09-26 : 09:09:40

You could try something like this:

set nocount on
create table #a( a int identity( 1, 1 ), FromVal int, ToVal int )
insert into #a select 10, 15
insert into #a select 37, 44

select IDENTITY(int, 1,1) AS ID, s.Counter
into #output
from #a join sequence s on s.counter between #a.FromVal and #a.ToVal

select * from #output

drop table #a
drop table #output

Note that this depends on the use of a sequence table. For more info on the use of such tables, you can search this site for "number table", "sequence table", or "tally table".

Dennis

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-26 : 09:11:50

You need to follow this link and format your question more like this and I'm sure you'll get some answer. Read the entire topic and see how many (quick) responses this person get when they provide this kind of information.

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29085[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources