Duplicating rows with numbers

[alanmusician]

I have a table with a text field containing a filename and a count of the number of images in the filename. I want there to be a numbered record for each image. For example:

Imgfile     | ImageNo
000020.tif  | 3

Needs to be:

Imgfile     | ImageNo
000020.tif  | 1
000020.tif  | 2
000020.tif  | 3

How is this best accomplished?

 

[Glowworm27]

I dont understand your question.

Do you want a unique number for each filename?

you would set that column to an Identity column and increment by 1.

You cannot count images in a filename from SQL, so That is where the confusion lies.

to create an identity column use the following script

CREATE TABLE myTable1
	(
        ImgFile char(20) NOT NULL,
	ImageNo int NOT NULL IDENTITY (1, 1)
	)  ON [PRIMARY]
GO

hope this helps


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[gmmastros]

You can create a number table to accomplish this.

Declare @MaxImageNo Integer
Declare @i Integer

Select @i = 1,
       @MaxImageNo = Max(ImageNo)
From   [!]YourTable[/!]

Declare @Numbers Table(Number Integer)

While @i <= @MaxImageNo
  Begin
    Insert Into @Numbers Values(@i)
    Set @i = @i + 1
  End

Select N.Number, [!]YourTable[/!].ImgFile 
From   @Numbers N
       Full outer Join [!]YourTable[/!]
          On N.Number <= [!]YourTable[/!].ImageNo
Order By imgFile, Number

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Full outer?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

What? You prefer Inher? I tested it all ways... Inner outer, left, right, they all give the same results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

> I tested it all ways... Inner outer, left, right, they all give the same results.

Yes, because Numbers table has 3 rows. If that's not the case (in reality Numbers table is static with much more records, right?)....

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

Because I dynamically create the @Numbers table.

[tt][blue]Select @i = 1,
@MaxImageNo = Max(ImageNo)
From YourTable

[!]Set @MaxImageNo = @MaxImageNo + 1[/!]

Declare @Numbers Table(Number Integer)

While @i <= @MaxImageNo
Begin
Insert Into @Numbers Values(@i)
Set @i = @i + 1
End[/blue][/tt]

If there are more numbers in the numbers table than there are matches in the YourTable, then you will get extra rows. With the line shown in [!]red[/!], you will get an extra row. Of course, you're right. the full outer should be an inner.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom