Repeated Entries "Off" - 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)

Repeated Entries "Off"

Author

Topic

karthi2009
Starting Member

23 Posts

Posted - 2009-07-15 : 07:52:31

Hi Team,

For a particular problem, I am getting a SQL output like this :
--------------------
Status | Description
--------------------
Open | Desc1
Open | Desc2
Closed | Desc3
Closed | Desc4
Closed | Desc5
--------------------Here the value in the 'Status' is repeating for multiple times.
So, I wanted to show only the first entry and remaining all blank as below:

--------------------
Status | Description
--------------------
Open | Desc1
(Blank)| Desc2
Closed | Desc3
(Blank)| Desc4
(Blank)| Desc5
--------------------

'Blank' represents repetition of the same Statuses..
How can I obtain this kind of output..
Please help me in this regard..

Thanks in advance..

Karthikeyan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 07:59:07

[code]
declare @tab table(Status varchar(32), Description varchar(32))
insert into @tab select

'Open','Desc1' union all select
'Open','Desc2' union all select
'Closed','Desc3' union all select
'Closed','Desc4' union all select
'Closed','Desc5'

select * from @tab

select case when rid = 1 then status else null end as status,description
from (select row_number()over(partition by status order by description ) as rid , * from @tab)s
order by description
[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-15 : 08:10:49

Just in case if you want to update a physical table then you can use the parttition function as suggested by bklr & then use a CTE to do the update.


declare @tab table(Status varchar(32), Description varchar(32))
insert into @tab select

'Open','Desc1' union all select 
'Open','Desc2' union all select 
'Closed','Desc3' union all select 
'Closed','Desc4' union all select 
'Closed','Desc5'

;with ctetab
as
(
select row_number()over(partition by status order by description ) as rid , * from @tab
)
update ctetab set Status=case when rid=1 then Status else null end

select * from @tab

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 08:14:07

hi ayamas,
here no need of the cte u can directly update the table
update s
set status = case when rid = 1 then s.status else null end
from (
select row_number()over(partition by status order by description ) as rid , * from @tab)s

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-15 : 08:16:29

Oops missed that one.Thanks for pointing.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 08:35:21

welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 13:37:15

This seems like a presentation issue and might be better to do this at front end. This is called suppress duplicate values in front end tools like reporting tools.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources