How to do this without using Cursor/SP/View

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

How to do this without using Cursor/SP/View

Author

Topic

Ltran
Starting Member

4 Posts

Posted - 2005-09-28 : 21:39:37

Hi, everyone. I'm a beginner so my question might be silly.

If I have a table as below:

Letter No.
A 1
B 2
C 2

How to write a query without using Cursor/SP/View to return:

Letter No.
A 1
B 2
B 3
C 4
C 5

Thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-28 : 21:44:59

The requirements don't make any sense, so it must be HOMEWORK.

CODO ERGO SUM

Ltran
Starting Member

4 Posts

Posted - 2005-09-28 : 21:49:08

Sorry what don't make any sense?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-28 : 23:29:55

The requirement to "write a query without using Cursor/SP/View to return". Why would that be a requirement for a real world application? So the only conclusion is that this is a homework problem for a class.

CODO ERGO SUM

Ltran
Starting Member

4 Posts

Posted - 2005-09-29 : 00:03:47

Well, my SQL Server login account is rectricted by the DBA so that I can not create SP, View. I can only use simple Select command.

Don't think that I'm s student looking for an answer to a homework. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 01:59:21

Can you tell us the logic you want to use to get those result?

Madhivanan

Failing to plan is Planning to fail

Ltran
Starting Member

4 Posts

Posted - 2005-09-29 : 02:39:45

Madhivanan, thanks for helping a beginner.

Refer to my example, I hope I can explain it well

If The No. Column = 1, then return as it is, i.e Letter A with No. 1
If The No. Column > 1, then return the Letter with No. 1 n times until n = No., i.e Letter 'B' and 'C' with No. 2

Suppose I have another table

Letter No.
A 5
B 2
C 3
D 1

My query would return

Letter No.
A 1
A 1
A 1
A 1
A 1
B 1
B 1
C 1
C 1
C 1
D 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 02:48:03

Post the query you used

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-29 : 06:23:38

I don't think he can restrict you from using a cursor.

Also, I don't think he can prevent you from creating a temporay stored procedure ( create proc #temp_procname...) and executing that.

quote:
Originally posted by Ltran

Well, my SQL Server login account is rectricted by the DBA so that I can not create SP, View. I can only use simple Select command.

Don't think that I'm s student looking for an answer to a homework. Thanks

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources