May 23, 2008 at 4:39 am
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
May 23, 2008 at 7:25 am
There are a number of ways to do this. The first way -- probably the best way in this case -- is to do all of the work in the INSERT statement and not use an update statment at all. This can be done by pivoting the original table and using the piovoted query as the basis for the insert. Give a look at the PIVOT clause in books online. The update statement can also be derived based on pivoting the table.
Another alternative is to perform a multiple self-join, but my knee-jerk reaction is that the pivoting might be a bit quicker.
May 23, 2008 at 7:50 am
[p]I suspect that something like this would do the trick. I haven't tested it because I haven't the time to do the sample code![/p]
[font="Courier New"]
SELECT
[compound_id]=compound_ID,
[compound_name] =MAX(CASE WHEN name_Type=1 THEN name ELSE '' END),
[compound_desc] =MAX(CASE WHEN name_Type=2 THEN name ELSE '' END),
[compound_synonym] =MAX(CASE WHEN name_Type=3 THEN name ELSE '' END),
[compound_formula] =MAX(CASE WHEN name_Type=4 THEN name ELSE '' END),
[compound_trade_nme] =''
FROM compounds GROUP BY compound_ID
[/font]
Best wishes,
Phil Factor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply