Help with looping - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with looping

Author

Topic

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-05-23 : 06:27:46

Hello clever people
I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this
name
compound_id integer
name varchar(150)
name_type integer

This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types
1 = chemical name
2 = a description of the chemical
3 = a synonym of the chemical
4 = a formula of the chemical

I have created a new table called compound_name with this structure

id int primary key (auto identity)
compound_id int used as a foreign key
compound_name varchar(150)
compound_desc varchar(250)
compound_synonym varchar(150)
compound_formula varchar(50)
compound_trade_nme varchar(50)

I have also started to populate the new table by running this code
insert into compound_name(compound_id,compound_name)
SELECT DISTINCT compound_id, name
FROM dbo.name
WHERE (name_type = 1)

Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2
Then case 3 do the same for name_type 3 which is the name_type for descripton
Then case 4 do the same for name_type 4 which is the formula

Any help would be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 06:49:31

Can you illustrate this with help of some sample data?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 06:51:07

more detail is needed - I'm not certain (yet) that you need to do this with loops

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

aprichard
Yak Posting Veteran

62 Posts

Posted - 2008-05-23 : 07:10:03

Can u give query what u got

WelshPunk
Yak Posting Veteran

67 Posts

Posted - 2008-05-23 : 08:01:39

Hello chaps

Just spoken to the application owner and given them some stats on the different naming types and how many of each are in db. He has decided that we can do away with the name_types field altogether (hooray!)

Thanks anyway

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 08:42:46

Do you have multiple DISTINCT values for NAME for the SAME Compound_Id,Name_Type_Id pair? I assume not.

Have a look at this, and see if it works for you:


Create Table DupsBeHere
(compound_id integer,
[name] varchar(150),
name_type integer)
go

insert into DupsBeHere (compound_id,[name],name_type) values (1,'a1',1)
insert into DupsBeHere (compound_id,[name],name_type) values (1,'a2',2)
insert into DupsBeHere (compound_id,[name],name_type) values (1,'a3',3)
insert into DupsBeHere (compound_id,[name],name_type) values (1,'a4',4)

insert into DupsBeHere (compound_id,[name],name_type) values (2,'B1',1)
insert into DupsBeHere (compound_id,[name],name_type) values (2,'B2',2)
insert into DupsBeHere (compound_id,[name],name_type) values (2,'B2',3)
insert into DupsBeHere (compound_id,[name],name_type) values (2,'B4',4)

CREATE TABLE compound_name (
id int identity(1,1) primary key,
compound_id int,
compound_name varchar(150),
compound_desc varchar(250),
compound_synonym varchar(150),
compound_formula varchar(50),
compound_trade_nme varchar(50))

INSERT INTO compound_name (compound_id,compound_name,compound_desc,compound_synonym,compound_formula)
SELECT
	compound_id, 
	max(case when name_type = 1 then [name] else NULL end) as compound_name, 
	max(case when name_type = 2 then [name] else NULL end) as compound_desc, 
	max(case when name_type = 3 then [name] else NULL end) as compound_synonym, 
	max(case when name_type = 4 then [name] else NULL end) as compound_formula
FROM	DupsBeHere
group by compound_id

SELECT * from compound_name

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 08:43:37

that's what I get for waiting until lunch to work on it :d

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources