How do I remove repeated column values from Table

Question

select * from TableName

FROM THIS:

ID Col1 Col2 Col3 Col4 Col5 1 aa 21 ss m p 2 aa 21 tt f u 3 bb 21 ss f d 4 bb 22 ss m d

TO THIS

ID Col1 Col2 Col3 Col4 Col5 1 aa 21 ss m p 2 tt f u 3 bb ss d 4 22 m

i want this Output is it possible ?

don't want repeat duplicate value if it next to the same

Thanks for your time.

refer this, this may help you stackoverflow.com/questions/4189218/… — SRIRAM
– SRIRAM, Commented Nov 29, 2012 at 11:24
here i used Break on Stud_name select ID,Stud_Name,Stud_Age,Stud_Addr,Stud_Gender,Stud_Qualification from student order by ID but error is Msg 135, Level 15, State 1, Line 1 Cannot use a BREAK statement outside the scope of a WHILE statement. — MSR
– MSR, Commented Nov 29, 2012 at 11:29

Michał Powaga · Accepted Answer · 2012-11-29 12:05:03Z

You can select everything you want to temporary table, clear repeated values and return result set from updated temporary table.

Query might look like this:

select 1 as id, 'aa' as col1, '21' as col2,'ss' as col3,'m' as col4,'p' as col5 into #tmp union select 2, 'aa','21','tt','f','u' union select 3, 'bb','21','ss','f','d' union select 4, 'bb','22','ss','m','d' update t set col1 = case when tp.col1 = t.col1 then '' else t.col1 end, col2 = case when tp.col2 = t.col2 then '' else t.col2 end, col3 = case when tp.col3 = t.col3 then '' else t.col3 end, col4 = case when tp.col4 = t.col4 then '' else t.col4 end, col5 = case when tp.col5 = t.col5 then '' else t.col5 end from #tmp t left join ( select t1.id, max(t2.id) as pid from #tmp t1 join #tmp t2 on t1.id > t2.id group by t1.id ) p on t.id = p.id left join #tmp tp on p.pid = tp.id

Result:

/*------------------------ select top 100 * from #tmp ------------------------*/ id col1 col2 col3 col4 col5 1 aa 21 ss m p 2 tt f u 3 bb ss d 4 22 m

SQL Fiddle

TechDo · Accepted Answer · 2012-11-29 12:03:50Z

Please check the query, is this what you need?:

update a set a.Col1=(case when cnt1=1 then a.Col1 else null end), a.Col2=(case when cnt2=1 then a.Col2 else null end), a.Col3=(case when cnt3=1 then a.Col3 else null end) from TableName a join ( select *, row_number() over (Partition By Col1 order by ID) cnt1, row_number() over (Partition By Col2 order by ID) cnt2, row_number() over (Partition By Col3 order by ID) cnt3 From TableName ) x on a.ID=x.ID

hello bro its updating every entry i want only col wise if next element is same as previous so please review this once again

Collectives™ on Stack Overflow

How do I remove repeated column values from Table

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related