Duplicate Records Based on Value in One Field

[ar0927]

I have a table that currently has multiple records for each [itemid]. I am trying to append these records to another table, but only want to append one record for each [itemid] (which will essentially remove the "duplicates"). Please note that these "duplicate" rows are not identical. Instead, they actually contain slightly different data is some of the fields. I attempted to write a SQL query using DISTINCT, but could not get it to work due to the unique values in other fields.

The table has over 30 fields. I need to append all 30 fields to the second table, based on the unique value in the field [itemid].

Any ideas would be appreciated!
Thanks in advance for your assistance!

[June7]

Makes no sense. If you need to append data from 30 fields and if any one of them changes within item group, you do not have duplicate records. Show example source data and desired output.

[ar0927]

I apologize for my confusing post and I apologize for using the word duplicate when in fact they are not completely identical records. I did, however, put the word duplicates in quotes in my original post and explained that the [itemid] is duplicated, but other fields are unique because I do understand they are not true duplicates (hence why I cant use SQL DISTINCT). Let me know if this makes more sense...


I have a table that currently has multiple records for each [itemid]. I am trying to append these records to another table, but only want to append one record for each [itemid].

The table has over 30 fields. I need to append all 30 fields to the second table, based on the unique value in the field [itemid].

Original Data:

buid	itemid	bi_status	typical_lead_time	abcclass	avg_mo_usage	ecfactor	suom	euom	pcfactor	puom	Field12	puom_cost	euom_cost	suom_cost	facility	f_min	f_max	locator	l_min	l_max	commodity_major_id	commodity_minor_id	commodity	gl_acct	account_name	ei_status	vendor_cd	vendor_name	vendor_catno	ei_description	bu_description
1000	1234	A	5	B	6.867246	1	BX	BX	1	BX	BX	148.95	148.95	148.95	FACNAME	4	6	W1R	4	6	17	95	Reagents/Manual Testing	123456	Chemical Reagents	A	VENDORID	VENDOR NAME	123456789	DESCRIPTION	DESCRIPTION
1000	56789	A	1	A	112.382307	1	EA	EA	1	EA	EA	111.81	111.81	111.81	FACNAME	52	84	W1A	52	84	17	90	Reagents/Assays	123456	Chemical Reagents	A	VENDORID	VENDOR NAME	98765432	DESCRIPTION	DESCRIPTION
1000	11222	A	1	C	51.878084	1	PK	PK	0.1	CA	PK	33.11	3.311	3.311	FACNAME	20	40	REM2	0	0	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	55512345	DESCRIPTION	DESCRIPTION
1000	11222	A	1	C	51.878084	1	PK	PK	0.1	CA	PK	33.11	3.311	3.311	FACNAME	0	0	T1A	5	10	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	55512345	DESCRIPTION	DESCRIPTION
1000	11222	A	1	C	51.878084	1	PK	PK	0.1	CA	PK	33.11	3.311	3.311	FACNAME	20	40	LGSTCS	20	30	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	55512345	DESCRIPTION	DESCRIPTION
1000	22244	A	1	B	712.380928	1	PK	PK	0.025	CA	PK	64.71	1.61775	1.61775	FACNAME	0	0	T1A	10	20	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	9998765	DESCRIPTION	DESCRIPTION
1000	22244	A	1	B	712.380928	1	PK	PK	0.025	CA	PK	64.71	1.61775	1.61775	FACNAME	400	600	REM2	400	600	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	9998765	DESCRIPTION	DESCRIPTION

Data that I would like to Append to another table:

buid	itemid	bi_status	typical_lead_time	abcclass	avg_mo_usage	ecfactor	suom	euom	pcfactor	puom	Field12	puom_cost	euom_cost	suom_cost	facility	f_min	f_max	locator	l_min	l_max	commodity_major_id	commodity_minor_id	commodity	gl_acct	account_name	ei_status	vendor_cd	vendor_name	vendor_catno	ei_description	bu_description
1000	1234	A	5	B	6.867246	1	BX	BX	1	BX	BX	148.95	148.95	148.95	FACNAME	4	6	W1R	4	6	17	95	Reagents/Manual Testing	123456	Chemical Reagents	A	VENDORID	VENDOR NAME	123456789	DESCRIPTION	DESCRIPTION
1000	56789	A	1	A	112.382307	1	EA	EA	1	EA	EA	111.81	111.81	111.81	FACNAME	52	84	W1A	52	84	17	90	Reagents/Assays	123456	Chemical Reagents	A	VENDORID	VENDOR NAME	98765432	DESCRIPTION	DESCRIPTION
1000	11222	A	1	C	51.878084	1	PK	PK	0.1	CA	PK	33.11	3.311	3.311	FACNAME	20	40	REM2	0	0	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	55512345	DESCRIPTION	DESCRIPTION
1000	22244	A	1	B	712.380928	1	PK	PK	0.025	CA	PK	64.71	1.61775	1.61775	FACNAME	0	0	T1A	10	20	20	40	Specimen Bags	567890	Specimen Collection Supplies	A	VENDORID	VENDOR NAME	9998765	DESCRIPTION	DESCRIPTION

NOTE: It doesn't matter which of the repeated [itemid] records are chosen.


Any ideas would be appreciated!
Thanks in advance for your assistance!

[June7]

If it doesn't matter which [locator] value is saved, of what use is this data (same for other fields)?

Build an aggregate query that returns the buid and ItemID fields as well as Max() or Min() or First() or Last() value from each of the other fields and GROUP BY the buid and ItemID fields.

Use that query as source for the APPEND.