creating new table from counter column

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)

creating new table from counter column

Author

Topic

SQuirreL_
Starting Member

1 Post

Posted - 2006-01-18 : 20:32:29

Hi,

Feeling really helpless on how to approach this one:

I'm using:
SQL Server 2000
Query Analyser or Enterprise Manager

I've got a registration db table where users register to enter a competition draw, with a counter column next to their entry which increments (+ 1 point) for each person they refer to the competition (giving them an extra entry into the compition per referral).

For the competition winner draw, I need to creat a new table (comp draw table) with a separate row for each registered user for each referral (counter) against their entry.
Thus, rows in draw table = registered user x respective counter.

Your input is much appreciated!
J.

kristianwhittick
Starting Member

12 Posts

Posted - 2006-06-06 : 11:29:23

1) Use a cursor to loop through the rows and get the counter
2) Create an inner loop for each counter.
3) within the inter loop call INSERT.

(Are you sure you want to create all these new records..
There may be a better way.)
Kris

Example below.

CREATE TABLE test (
PersonName varchar(10),
Counter int
)
GO

CREATE TABLE testb (
PersonName varchar(10)
)
GO

Insert into test
VALUES ( 'A', 4);

Insert into test
VALUES ( 'B', 2);
GO

DECLARE @OneName varchar(10)
DECLARE @OneCounter int

DECLARE my_cursor CURSOR FOR
SELECT PersonName, Counter FROM test;

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @OneName, @OneCounter

WHILE @@FETCH_STATUS = 0
BEGIN

WHILE (@OneCounter > 0)
BEGIN
INSERT INTO testb VALUES (@OneName);

SET @OneCounter = @OneCounter - 1;
END;

FETCH NEXT FROM my_cursor INTO @OneName, @OneCounter
END

CLOSE my_cursor
DEALLOCATE my_cursor
GO

SELECT * from testb;
GO

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-06 : 11:50:47

quote:
1) Use a cursor

This is easy with a numbers/tally table...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

--data
declare @test table (PersonName varchar(10), Counter int)
insert @test 
          select 'A', 4
union all select 'B', 2

declare @testb table (PersonName varchar(10))

--calculation
declare @MaxCounter int
select @MaxCounter = max(counter) from @test

insert @testb
select PersonName from @test a
    inner join dbo.F_TABLE_NUMBER_RANGE(1, @MaxCounter) b on b.number <= a.Counter
order by PersonName

select * from @testb

/*results
PersonName 
---------- 
A
A
A
A
B
B
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources