Getting a sequence number - 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)

Getting a sequence number

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 08:53:13

declare @tbl as table(id int)
insert into @tbl
select 1 union all
select 1 union all
select 1 union all
select 1

I need to get an output in this form
1
11
111
1111

depending on number of rows that are present in the table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 08:58:14

[code]declare @tbl as table(id int)
insert into @tbl
select 7 union all
select 3 union all
select 5 union all
select 1

;WITH Yak (id, recid)
AS (
SELECT CAST(id as varchar(max)),
row_number() over (order by id)
from @tbl
), Peso (pat, recid)
AS (
SELECT id,
recid
from yak
where recid = 1

union all

SELECT p.pat + y.id,
y.recid
from yak as y
inner join peso as p on p.recid = y.recid - 1
)

select pat
from peso[/code]

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 09:04:29

quote:

Originally posted by Peso

declare @tbl as table(id int)
insert into @tbl
select 7 union all
select 3 union all
select 5 union all
select 1 

;WITH Yak (id, recid)
AS (
	SELECT	CAST(id as varchar(max)),
		row_number() over (order by id)
	from	@tbl
), Peso (pat, recid)
AS (
	SELECT	id,
		recid
	from	yak
	where	recid = 1

	union all

	SELECT		p.pat + y.id,
			y.recid
	from		yak as y
	inner join	peso as p on p.recid = y.recid - 1
)

select	pat
from	peso

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

Exactly 5 mins & 1 second to get the solution.Even if I had spent 5 hours on it I still would not had the result.
Thanks once again

P.S Now even Thanks seems to be a much smaller word for thanking.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:07:08

Send me a snailmail postcard then?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 09:08:47

Looks like i am too late . . .


declare @tbl as table(id int)
insert into @tbl
select 1 union all
select 1 union all
select 1 union all
select 1 

;with data (id, row_no)
as 
(
	select	id = convert(varchar(10), id), row_no = row_number() over (order by id)
	from	@tbl
)
select  id = (SELECT '' + id FROM data AS x 	
		WHERE x.row_no <= d.row_no FOR XML PATH(''))
from	data d

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 09:09:31

If you dont mind giving me your postal address :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:10:28

Done!

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 09:18:53

quote:
Originally posted by ayamas

If you dont mind giving me your postal address :)

Who need postal addr when you have the GPS coordinate

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 09:24:31

Sorry Peso this time it seems I will have to use khtan solution as it looks more elegant.
Thanks khtan.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-01 : 09:27:49

select replicate('1',row_number() over(order by id) )
from table;

or am I missing something (not in front of SQL so could be way off here)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 09:32:36

Well its getting much better & simple.I am gonna use LoztInSpace solution if someone can post much simpler than this one which I doubt.
Thanks to all of you guys.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:37:27

Oh, there are only '1' in the production table?
The four '1' is by design, not bad sample data?

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 09:51:29

quote:
Originally posted by Peso

Oh, there are only '1' in the production table?
The four '1' is by design, not bad sample data?

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

Yes thats right.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:53:05

What if there were four '2'?
Should then the result be

2
22
222
2222

???

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:53:39

If that's the case, use LoztInSpace's suggestion and replace hardwired '1' with ID column name instead.

SELECT	REPLICATE(ID, ROW_NUMBER() OVER (ORDER BY ID)) 
FROM	@tbl

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 10:00:54

Yes I did that.Thanks to all you guys.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 10:54:00

Is there any way by which it can be done on distinct id
say
declare @tbl as table(id int)
insert into @tbl
select 1 union all
select 2 union all
select 1 union all
select 2 union all
select 3 union all
select 3 union all
select 3
o/p shud be
1
11
2
22
3
33
333

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 11:08:22

[code];with data (id, row_no)
as
(
select id = convert(varchar(10), id), row_no = row_number() over (partition by id order by id)
from @tbl
)
select id = (SELECT '' + id FROM data AS x
WHERE x.id = d.id and x.row_no <= d.row_no FOR XML PATH(''))
from data d[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 11:12:11

using LoztInSpace's replicate() method. The CTE part is the same.


;with data (id, row_no)
as 
(
	select	id = convert(varchar(10), id), row_no = row_number() over (partition by id order by id)
	from	@tbl
)
select	replicate(id, row_no)
from	data

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 11:15:49

or modify from Peso's last query


SELECT	REPLICATE(ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)) 
FROM	@tbl

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-01 : 13:04:34

quote:
Originally posted by khtan

or modify from Peso's last query

SELECT REPLICATE(ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID))
FROM @tbl

KH
[spoiler]Time is always against us[/spoiler]

Oh it was that simple.Thank you khtan for all the help.
You guys are great.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources