| Author |
 Topic  |
|
|
ayu
Starting Member
43 Posts |
 Posted - 02/10/2009 : 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
United Kingdom
12543 Posts |
Posted - 02/10/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 02/10/2009 : 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
India
600 Posts |
Posted - 02/10/2009 : 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
India
333 Posts |
Posted - 02/11/2009 : 00:12:12
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 02/11/2009 : 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 - 02/11/2009 : 09:11:05
|
Thanks to all of you..i got my solution.
its not for reporting purpose.
thanks to all.. |
|
|
| |
Topic |
|
|
|
no duplication
