Help 'writing a Select query' - 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)

Help 'writing a Select query'

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-19 : 02:31:26

Table

ID code Name
1 10 a
1 20 a
1 30 a
2 10 b
2 50 b
2 30 b
2 20 b
3 40 c
4 10 d
4 30 d

desired output

1 *** a
2 *** b
3 40 c
4 *** d

i need to write a single select statement, so that when there are multiple codes for 1 ID
the in output the need to replace it with "***" and if there is only one code for the id
then i need to display the code in the result

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 03:30:43

Where do you want to display data?
Return data with count and when you show check if count exceeds 1 and display *** accordingly

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2007-11-19 : 03:37:07

For table structure like below

create table #temp
(

id int,
code int,
[Name] varchar(10)
)

try this query

select Id, [Name],
Case
when ((select count(code) from #temp t1 where t1.Name=t2.name) >1) then
'***'
else
(select cast(code as varchar) from #temp t1 where t1.Name=t2.name )
End
from #temp t2 group by [Name],Id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 03:41:03

As this is posted in SQL Server 2005, this should work

select id,max(case when counting=1 then cast(code as varchar(10)) else '***' end),max(name) as name from
(
	select *,count(*) over(partition by id) as counting from table
) as t
group by id

But as I said in the first reply try to do this in front end application

Madhivanan

Failing to plan is Planning to fail

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-19 : 04:00:54

i display the result on a excel report ,so i cant do any changes in front end

kneekill
Yak Posting Veteran

76 Posts

Posted - 2007-11-19 : 06:51:44

quote:
Originally posted by madhivanan

As this is posted in SQL Server 2005, this should work
select id,max(case when counting=1 then cast(code as varchar(10)) else '***' end),max(name) as name from
(
select *,count(*) over(partition by id) as counting from table
) as t
group by id

But as I said in the first reply try to do this in front end application

Madhivanan

Failing to plan is Planning to fail

thanks a lot Madhivanan this query worked perfectly in 2005
is there an equivalent for the "partition" in 2000..?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 07:09:39

<<
is there an equivalent for the "partition" in 2000..?
>>

No. You need other methods such aggregated subqueries,etc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources