SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 no duplication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ayu
Starting Member

43 Posts

Posted - 02/10/2009 :  11:57:48  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 02/10/2009 :  13:07:20  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 02/10/2009 :  23:48:26  Show Profile  Reply with Quote
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
Go to Top of Page

Nageswar9
Aged Yak Warrior

India
600 Posts

Posted - 02/10/2009 :  23:56:34  Show Profile  Reply with Quote
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'

Go to Top of Page

Jai Krishna
Constraint Violating Yak Guru

India
333 Posts

Posted - 02/11/2009 :  00:12:12  Show Profile  Reply with Quote


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'



Jai Krishna
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2009 :  03:29:42  Show Profile  Reply with Quote
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.
Go to Top of Page

ayu
Starting Member

43 Posts

Posted - 02/11/2009 :  09:11:05  Show Profile  Reply with Quote
Thanks to all of you..i got my solution.

its not for reporting purpose.

thanks to all..
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000