removing duplicate row values

Question

Post reply

removing duplicate row values

chandresh soni

Grasshopper

Points: 22
More actions
August 4, 2009 at 3:26 am

#134563

hi
I have a result set from a qry like below
ORDER_NO | ORDER_TITLE | ORDER_DETAIL | ORDER_DETAIL_no | AMOUNT
1 1.1 XXX XXX.1 200
2 2.1 ppp ppp.1 100
3 3.1 yyy yyy.1 200
3 3.1 yyy yyy.2 100
3 3.1 yyy yyy.3 200
4 4.1 zzz zzz.1 100
4 4.1 zzz zzz.2 100
i need the output where i remove duplicate values for a order no and like below
ORDER_NO | ORDER_TITLE | ORDER_DETAIL | ORDER_DETAIL_no | AMOUNT
1 1.1 XXX XXX.1 200
2 2.1 ppp ppp.1 100
3 3.1 yyy yyy.1 200
-- --- --- yyy.2 100
-- --- --- yyy.3 200
4 4.1 zzz zzz.1 100
-- --- --- zzz.2 100
any idea what is the best way to do
/chandresh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

Where do you want to show your data?

Madhivanan

Failing to plan is Planning to fail

chandresh soni Grasshopper Points: 22 More actions · Answer 2

chandresh soni

Grasshopper

Points: 22

August 4, 2009 at 7:31 pm

#1034579

inside a Devexpress grid for ASP.NET

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 3

Take just five minutes to help us help you. Please read the first link in my signature below. Provide a CREATE TABLE statement and data like in the article and you'll have people that will very quickly give you a tested working solution. I know... I'm one of those people. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arun.sas SSChampion Points: 11831 More actions · Answer 4

chandresh soni (8/4/2009)
ORDER_NO | ORDER_TITLE | ORDER_DETAIL | ORDER_DETAIL_no | AMOUNT
1 1.1 XXX XXX.1 200
2 2.1 ppp ppp.1 100
3 3.1 yyy yyy.1 200
-- --- --- yyy.2 100
-- --- --- yyy.3 200
4 4.1 zzz zzz.1 100
-- --- --- zzz.2 100

Hi,

try this

create table #temp1

(

ORDER_NO int,

ORDER_TITLE varchar(3),

ORDER_DETAIL varchar(3),

ORDER_DETAIL_no varchar(5),

AMOUNT int

)

insert into #temp1

select 1,'1.1', 'XXX', 'XXX.1', 200

union all

select 2,'2.1', 'PPP', 'PPP.1', 100

union all

select 3,'3.1', 'YYY', 'YYY.1', 200

union all

select 3,'3.1', 'YYY', 'YYY.2', 100

union all

select 3,'3.1', 'YYY', 'YYY.3', 200

union all

select 4,'4.1', 'ZZZ', 'ZZZ.1', 100

union all

select 4,'4.1', 'ZZZ', 'ZZZ.2', 200

update a

set a.order_no = '',

a.ORDER_TITLE = '--',

a.ORDER_DETAIL = '--'

from #temp1 a,

(

select ORDER_NO,ORDER_DETAIL_no

from #temp1

where ORDER_DETAIL_no like '%.1'

)b

where a.ORDER_NO = b.ORDER_NO

and a.ORDER_DETAIL_no b.ORDER_DETAIL_no