Help with select statement - 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 2005 Forums

Transact-SQL (2005)

Help with select statement

Author

Topic

skylimitsql
Starting Member

8 Posts

Posted - 2010-09-02 : 06:27:30

I like to create a select statement which would give me results like this
1xx
2xx
3xx
4xx
5xx
6xx
7xx
8xx
9xx
Axx
Bxx
Cxx
Dxx
Fxx
0xx
1xx
2xx
3xx
4xx
5xx
6xx
7xx
8xx
9xx
Axx
....
....

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 06:42:23

[code]SELECT d.Chr + 'xx'
FROM (
SELECT '0' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F'
) AS d(Chr)[/code]

N 56°04'39.26"
E 12°55'05.63"

skylimitsql
Starting Member

8 Posts

Posted - 2010-09-02 : 06:51:02

Thanks for your help.
But I need to put it into loop. I need to control it rather then hard code.
Iteration must go on like --
0xx
1xx
2xx
3xx
4xx
5xx
6xx
7xx
8xx
9xx

Then
Axx
Bxx
Cxx
Dxx
Fxx

Then
0xx
1xx
2xx
3xx
4xx
5xx
6xx
7xx
8xx
9xx

Then
Axx
....
....
Fxx

Then
oxx
...
...
9xx

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 08:51:08

For how many times?

N 56°04'39.26"
E 12°55'05.63"

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-02 : 08:56:52

use this query
change top 30 rows to whatever number u need to repeat the bunch.
I mean if u need for 1 time top 15
2 times top 30
3 times than top 45 and so on

SELECT replace(replace(cast (master.dbo.fn_varbintohexstr(case when RowID > 15 then case when RowID%15=0 then 15 else RowID%15 end else RowID end) as nvarchar(20)),'0',''),'x','') + 'XX' from
(
select top 30
ROW_NUMBER() OVER (ORDER BY object_id ASC) AS ROWID
from sys.all_objects)
as a

skylimitsql
Starting Member

8 Posts

Posted - 2010-09-03 : 04:41:37

Many Many Thanks.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-03 : 04:45:40

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources