CASE statement and DUPLICATE problem in SQL

[pradchal1]

Earing Hold-Type Name Address

$100 Address John Italy
$100 DOB John Italy
$50 Address Mike Canada
$30 DOB Rose Dallas
$30 Address Rose Dallas
$200 Address Jenny New York



My desired output:

Earing Name Address

$100 John "Hold"
$50 Mike Canada
$30 Rose "Hold"
$200 Jenney "Hold"



I just want one row for each person. And on that row if the person has a Hold-Type = Address, I want his/her address to be "Hold".
How can I do this?
I can do this with CASE statement but I cannot make it appear just one row for each person.

Any help would be greatly appreciated.

Thanks

 

[hneal98]

You would need to break it up into two queries. First query the Address hold types and then the others. If the Address hold type already exists in a temp table, then do bring in the other with the same name.

 

[pradchal1]

Could you please elaborate?

Thanks

 

[hneal98]

What part didn't you understand?

 

[gmmastros]

I assumes that your example was wrong because your desired output had 'canada' as the address for Mike eventhough his hold-type is 'address'.

Try this query to see if it works for you.

Select 	Earing, Name, 'Hold' As Address
From 	TableName
Where	[Hold-Type] = 'Address'

Union

Select 	Earing, Name, Address
From	TableName
Where	[Hold-Type] <> 'Address'
		And Name Not In 
			(
			Select 	Name
			From 	TableName
			Where	[Hold-Type] = 'Address'
			)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pradchal1]

Gmmastro,
Your solution worked (awesome) but the response time was greatly slow.
I guess I will have to run this query in Access and import the output table.

Thanks to everyone who tried to help.

 

[SQLBill]

BTW-you might want to change your column names on two columns:

Earing probably should be Earning or Earnings

Name is a reserved or Key word and you will probably have to put square brackets around it [name] when you use it.

Just a suggestion.

-SQLBill

Posting advice: FAQ481-4875