Ungrouping the grouped summary table 1

[smin]

How do I revert the summary table grouped by a column to an ungrouped original table? For example;

I have a table, TABLE1, with two columns:
ID, Counts (both integers)
1 2
2 4
3 1
4 2
.....

The result table, TABLE2, should be just one column with each id repeated number of times corresponding to counts column;
ID
1
1
2
2
2
2
3
4
4
...

TABLE1 is the result the following query.

select ID, count(ID) as counts
into TABLE1
from TABLE2
group by ID

I only have TABLE1 and I need to recreate the TABLE2.

I thought I could do this with a simple proc or function but I have been wrestling with this problem for days and deadline is approaching. I need some help.

Any help would be greatly appreciated.

Thanks.

smin.

 

[JayKusch]

here ya go ...


DECLARE @ID AS Int
DECLARE @CountOf AS Int
DECLARE @CountOfMax AS Int
--
SET @CountOf = 1
SET @CountOfMax = 0

DECLARE DeGroup SCROLL CURSOR FOR
SELECT ID, CountOf
FROM TEST
ORDER BY ID

OPEN DeGroup

FETCH FIRST FROM DeGroup
INTO @ID, @CountOfMax

WHILE @@FETCH_STATUS = 0
BEGIN
DO_IT_AGAIN:

INSERT INTO TEST2 (ID) VALUES (@ID)

SET @CountOf = @CountOf + 1

IF @CountOf <= @CountOfMax

BEGIN
GOTO DO_IT_AGAIN
END
ELSE
BEGIN
SET @CountOf = 1

FETCH NEXT FROM DeGroup INTO @ID, @CountOfMax
END

END

CLOSE DeGroup
DEALLOCATE DeGroup


Thanks

J. Kusch

 

[swampBoogie]

select t1.id 
from table1 as t1,table1 as t2 
where t1.counts >= t2.id

works if your id's are continuous.

Otherwise you can create a number table.

 

[smin]

JayKusch,

Thank you very much. Your code did the job. Thanks.

smin.

 

[zhavic]

One little makeup

Remove this line with 'label':
DO_IT_AGAIN:

and change this:
BEGIN
GOTO DO_IT_AGAIN
END

to this:
BEGIN
CONTINUE
END

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.