no duplication - 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

SQL Server 2005 Forums

Transact-SQL (2005)

no duplication

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2009-02-10 : 11:57:48

i want no duplication of other 4 columns - ID, Fname, Mname, Lname just it should be appear once..but will all partner_types...

ID Fname Mname Lname Partner_type
---------------------------------------------------

9 Annie E Bellenger Other
9 Annie E Bellenger Friend
9 Annie E Bellenger Heart
9 Annie E Bellenger We can
9 Annie E Bellenger Drugs
9 Annie E Bellenger Newspaper
9 Annie E Bellenger Media
9 Annie E Bellenger Telivision

output will be like:-

ID Fname Mname Lname Partner_type
---------------------------------------------------

9 Annie E Bellenger Friend
Heart
We can
Drugs
Newspaper
Media
Telivision

does it possible? or i get only one row with max(partner_type) row...

or i have to do just group by max(partner_type) in query...

can anyone suggest?

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-10 : 13:07:20

select ID = case when seq = 1 then ID else null end ,
Fname = case when seq = 1 then Fname else null end ,
Mname = case when seq = 1 then Mname else null end ,
Lname = case when seq = 1 then Lname else null end ,
Partner_type
from
(select *, seq = rank() over (partition by ID, Fname, Mname, Lname order by Partner_type) from tbl ) a
order by ID, Fname, Mname, Lname, seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-10 : 23:48:26

try like this
declare @tab table(id int,Fname varchar(16), Mname varchar(16), Lname varchar(16), Partner_type varchar(16))
insert into @tab select
9, 'Annie','E','Bellenger','Other' union all select
9,'Annie','E','Bellenger','Friend' union all select
9,'Annie','E','Bellenger','Heart' union all select
9,'Annie','E','Bellenger','Wecan' union all select
9,'Annie','E','Bellenger','Drugs' union all select
9,'Annie','E','Bellenger','Newspaper' union all select
9,'Annie','E','Bellenger','Media' union all select
9,'Annie','E','Bellenger','Telivision'

select ID, Fname, Mname, Lname,partner_type
from (
select *,row_number() over (partition by ID, Fname, Mname, Lname order by id)as rid from @tab )a
where rid > 1

select ID = case when rid = 2 then ID else null end ,
Fname = case when rid = 2 then Fname else null end ,
Mname = case when rid = 2 then Mname else null end ,
Lname = case when rid = 2 then Lname else null end ,
Partner_type
from
(
select *,row_number() over (partition by ID, Fname, Mname, Lname order by id)as rid from @tab )a
where rid > 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-10 : 23:56:34

Try This Once


DECLARE @temp table (ID int,Fname varchar(32),Mname varchar(32), Lname varchar(32), Partner_type varchar(32))
insert into @temp
select 9, 'Annie', 'E' ,'Bellenger' ,'Other' union all
select 9, 'Annie', 'E' ,'Bellenger', 'Friend' union all
select 9, 'Annie', 'E', 'Bellenger', 'Heart' union all
select 9, 'Annie', 'E', 'Bellenger', 'We can' union all
select 9, 'Annie', 'E', 'Bellenger', 'Drugs' union all
select 9, 'Annie', 'E', 'Bellenger', 'Newspaper' union all
select 9, 'Annie', 'E', 'Bellenger', 'Media' union all
select 9, 'Annie', 'E', 'Bellenger', 'Telivision'


select 	(CASE when Partner_type like 'Friend' THEN id  else NULL end)id ,
		(CASE when Partner_type like 'Friend' THEN fname  else NULL end) fname,
		(CASE when Partner_type like 'Friend' THEN mname  else NULL end) mname,
		(CASE when Partner_type like 'Friend' THEN Lname  else NULL end) Lname,partner_type
		 from @temp where partner_type <>'other'

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 00:12:12

[code]

DECLARE @temp table (ID int,Fname varchar(32),Mname varchar(32), Lname varchar(32), Partner_type varchar(32))
insert into @temp
select 9, 'Annie', 'E' ,'Bellenger' ,'Other' union all
select 9, 'Annie', 'E' ,'Bellenger', 'Friend' union all
select 9, 'Annie', 'E', 'Bellenger', 'Heart' union all
select 9, 'Annie', 'E', 'Bellenger', 'We can' union all
select 9, 'Annie', 'E', 'Bellenger', 'Drugs' union all
select 9, 'Annie', 'E', 'Bellenger', 'Newspaper' union all
select 9, 'Annie', 'E', 'Bellenger', 'Media' union all
select 9, 'Annie', 'E', 'Bellenger', 'Telivision'
select replace(coalesce((CASE when Partner_type like 'Friend' THEN id else NULL end),''),0,'')id ,
(CASE when Partner_type like 'Friend' THEN fname else '' end) fname,
(CASE when Partner_type like 'Friend' THEN mname else '' end) mname,
(CASE when Partner_type like 'Friend' THEN Lname else '' end) Lname,partner_type
from @temp where partner_type <>'other'

[/code]

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 03:29:42

this seems like a formatting isue. if this is for reporting purpose, you can very easily achieve this using remove duplicates property available in reporting tool. no need to do this while retrieving data from db.

ayu
Starting Member

43 Posts

Posted - 2009-02-11 : 09:11:05

Thanks to all of you..i got my solution.

its not for reporting purpose.

thanks to all..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources