TSQL question- Opposite Of Group By

[blacklocist]

Hi All,

I am writing this just to even know if this is possible. What I would like to do is a UnGroup. Crap this is so hard to explain...okay I have a table in sql that is five columns long. The first Column is, ohh never mind. Copy and paste!!

NUM OBMS GP DESCRIPTION ROUTE
1 NULL 10293 Rib Cut Press
12 8567 10433 Baker Plate Plasma
5 863456 102345 Motor Mount Deep V

What I want it to do is to take the first row, look at the value in NUM column and replicate this exact row in another table.

So when all of this happens you should of 1 row of first row, 12 rows of the second row and 5 rows of the third row. A total of 18(1+12+5) rows in another table. I have been trying to do a sql statement to do all of this but haven't had the luck.

I do know I can do it in C# but performance is so MUCH faster if it's done in sql but if that is what I have to resort to I will. Any ideas would be appreciated!!

[blacklocist]

Here is a better layout of the sql table

[pty]

ok, i'm assuming you have an id column as your pk

i probably have loads of errors in here but don't have mssql to try it with, hopefully what im doing is apparent though.

declare @rowcounter	integer,
		@rowcount 	integer,
		@newrowcounter integer,
		@newrowcount integer
select  @rowcount = count(*)
from table1

set @rowcounter = 1

while @rowcounter =< @rowcount  --per row in table1
begin
	set @newrowcounter = 1
	select 	@newrowcount = NUM
			from from table1
			where id = @rowcounter
	--ok, now we have the number of new rows we wanna insert loop that many times
	while @newrowcounter =< @newrowcount
	begin
		insert into table2 (list all fieldnames here except id)
		select (corresponding ones from table1)
		@newrowcounter = @newrowcounter + 1
	end
	@rowcounter = @rowcounter + 1
end

[blacklocist]

Well this is all I got so far. I have fixed a couple minor things but the insert into syntax I am getting stuck on.

use barcodedb

declare @rowcounter	integer,
		@rowcount 	integer,
		@newrowcounter integer,
		@newrowcount integer
select  @rowcount = count(*)
from ExcelToSql

set @rowcounter = 1

while @rowcounter <= @rowcount  --per row in table1
begin
	set @newrowcounter = 1
	select 	@newrowcount = 'NUM'
			from ExcelToSql
			where NUM = @rowcounter
	--ok, now we have the number of new rows we wanna insert loop that many times
	while @newrowcounter <= @newrowcount
	begin
		insert into BarcodeMultiplexed(OBMS, GP, Description, Router)
		select (OBMS, GP, Description, Router)
		set @newrowcounter = @newrowcounter + 1
	end
	set @rowcounter = @rowcounter + 1
end

Here is the error message I keep getting,

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ','.

[pty]

missed the from and where clause off the select

use barcodedb

declare @rowcounter	integer,
		@rowcount 	integer,
		@newrowcounter integer,
		@newrowcount integer
select  @rowcount = count(*)
from ExcelToSql

set @rowcounter = 1

while @rowcounter <= @rowcount  --per row in table1
begin
	set @newrowcounter = 1
	select 	@newrowcount = 'NUM'
			from ExcelToSql
			where NUM = @rowcounter
	--ok, now we have the number of new rows we wanna insert loop that many times
	while @newrowcounter <= @newrowcount
	begin
		insert into BarcodeMultiplexed(OBMS, GP, Description, Router)
		select OBMS, GP, Description, Router
		from Table1 
		where id = @rowcounter
		set @newrowcounter = @newrowcounter + 1
	end
	set @rowcounter = @rowcounter + 1
end

[blacklocist]

Hey sorry for the late reply. The 4th holiday has gotten my schedule all thrown off. Well this is the error I get.

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'NUM' to data type int.

The NUM column is type int. So I believe it's actually trying to convert NUM instead of the column NUM to integer. Any ideas?

missed the from and where clause off the select

use barcodedb

declare @rowcounter	integer,
		@rowcount 	integer,
		@newrowcounter integer,
		@newrowcount integer
select  @rowcount = count(*)
from ExcelToSql

set @rowcounter = 1

while @rowcounter <= @rowcount  --per row in table1
begin
	set @newrowcounter = 1
	select 	@newrowcount = 'NUM'
			from ExcelToSql
			where NUM = @rowcounter
	--ok, now we have the number of new rows we wanna insert loop that many times
	while @newrowcounter <= @newrowcount
	begin
		insert into BarcodeMultiplexed(OBMS, GP, Description, Router)
		select OBMS, GP, Description, Router
		from Table1 
		where id = @rowcounter
		set @newrowcounter = @newrowcounter + 1
	end
	set @rowcounter = @rowcounter + 1
end

[pty]

select 	@newrowcount = 'NUM'
from ExcelToSql
where NUM = @rowcounter

this bit is wrong. it should be :

selec @newrowcount = NUM
from ExcelToSQL
where <the id field of your table, so it can scroll through> = @rowcounter

[blacklocist]

Hey,
Well did a little tweaking and added somemore stuff and she works beautifully. Thanks for your help & advice pty!