TSQL question- Opposite Of Group By

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 401,615 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,718 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Thread Tools Display Modes

Views: 4417 | Replies: 7

•

Join Date: Apr 2006

Posts: 87

Reputation: blacklocist is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

blacklocist blacklocist is offline

Offline

Junior Poster in Training

TSQL question- Opposite Of Group By

Jul 3rd, 2006

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!!

•

Join Date: Apr 2006

Posts: 87

Reputation: blacklocist is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

blacklocist blacklocist is offline

Offline

Junior Poster in Training

Re: TSQL question- Opposite Of Group By

Jul 3rd, 2006

Here is a better layout of the sql tableUntitled-1.jpg

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 481

Reputation: pty is on a distinguished road

Rep Power: 3

Solved Threads: 31

pty

Offline

Posting Pro in Training

Re: TSQL question- Opposite Of Group By

Jul 3rd, 2006

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.

Help with Code Tags

(Toggle Plain Text)

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

•

Join Date: Apr 2006

Posts: 87

Reputation: blacklocist is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

blacklocist blacklocist is offline

Offline

Junior Poster in Training

Re: TSQL question- Opposite Of Group By

Jul 3rd, 2006

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.

Help with Code Tags

(Toggle Plain Text)

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 ','.

Last edited by blacklocist : Jul 3rd, 2006 at 7:57 pm.

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 481

Reputation: pty is on a distinguished road

Rep Power: 3

Solved Threads: 31

pty

Offline

Posting Pro in Training

Re: TSQL question- Opposite Of Group By

Jul 4th, 2006

missed the from and where clause off the select

Help with Code Tags

(Toggle Plain Text)

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

Last edited by pty : Jul 4th, 2006 at 7:49 am.

•

Join Date: Apr 2006

Posts: 87

Reputation: blacklocist is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

blacklocist blacklocist is offline

Offline

Junior Poster in Training

Re: TSQL question- Opposite Of Group By

Jul 10th, 2006

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

Help with Code Tags

(Toggle Plain Text)

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?

•

Originally Posted by pty

missed the from and where clause off the select
Help with Code Tags

(Toggle Plain Text)
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
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

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 481

Reputation: pty is on a distinguished road

Rep Power: 3

Solved Threads: 31

pty

Offline

Posting Pro in Training

Re: TSQL question- Opposite Of Group By

Jul 11th, 2006

Help with Code Tags

(Toggle Plain Text)

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

this bit is wrong. it should be :

Help with Code Tags

(Toggle Plain Text)

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

•

Join Date: Apr 2006

Posts: 87

Reputation: blacklocist is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

blacklocist blacklocist is offline

Offline

Junior Poster in Training

Re: TSQL question- Opposite Of Group By

Jul 14th, 2006

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

•

Only community members can participate in forum threads. You must register or log in to contribute.

•

DaniWeb MS SQL Marketplace

•

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

•

Thread Tools	Display Modes
Show Printable Version Email this Page	Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

•

Similar Threads

c vs c++ (C++)
Gettig delphi to wait for a button press before continuing (Pascal and Delphi)

Other Threads in the MS SQL Forum

Previous Thread: Help Editing Trigs, Stored Procs, and Fkeys
Next Thread: Cannot edit the sql server from windows authentication to sql server authtentication

DaniWeb Home > Forums > Web Development > Databases > MS SQL

TSQL question- Opposite Of Group By

TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By

Re: TSQL question- Opposite Of Group By