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 Other9 Annie E Bellenger Friend9 Annie E Bellenger Heart9 Annie E Bellenger We can9 Annie E Bellenger Drugs9 Annie E Bellenger Newspaper9 Annie E Bellenger Media9 Annie E Bellenger Telivisionoutput will be like:-ID Fname Mname Lname Partner_type---------------------------------------------------9 Annie E Bellenger Friend Heart We can Drugs Newspaper Media Telivisiondoes 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_typefrom(select *, seq = rank() over (partition by ID, Fname, Mname, Lname order by Partner_type) from tbl ) aorder 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 thisdeclare @tab table(id int,Fname varchar(16), Mname varchar(16), Lname varchar(16), Partner_type varchar(16))insert into @tab select9, '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 )awhere rid > 1select 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_typefrom(select *,row_number() over (partition by ID, Fname, Mname, Lname order by id)as rid from @tab )awhere rid > 1 |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-02-10 : 23:56:34
|
Try This OnceDECLARE @temp table (ID int,Fname varchar(32),Mname varchar(32), Lname varchar(32), Partner_type varchar(32))insert into @tempselect 9, 'Annie', 'E' ,'Bellenger' ,'Other' union allselect 9, 'Annie', 'E' ,'Bellenger', 'Friend' union allselect 9, 'Annie', 'E', 'Bellenger', 'Heart' union allselect 9, 'Annie', 'E', 'Bellenger', 'We can' union allselect 9, 'Annie', 'E', 'Bellenger', 'Drugs' union allselect 9, 'Annie', 'E', 'Bellenger', 'Newspaper' union allselect 9, 'Annie', 'E', 'Bellenger', 'Media' union allselect 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 @tempselect 9, 'Annie', 'E' ,'Bellenger' ,'Other' union allselect 9, 'Annie', 'E' ,'Bellenger', 'Friend' union allselect 9, 'Annie', 'E', 'Bellenger', 'Heart' union allselect 9, 'Annie', 'E', 'Bellenger', 'We can' union allselect 9, 'Annie', 'E', 'Bellenger', 'Drugs' union allselect 9, 'Annie', 'E', 'Bellenger', 'Newspaper' union allselect 9, 'Annie', 'E', 'Bellenger', 'Media' union allselect 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.. |
|
|
|
|
|