How to do this task

Hi everybody :
I have a small problem,I have a table call ClientRef
ClRefNo Surname Forname Area
1254 Thomson Moore Central
1254 Thomsan Moore South
1253 Noel Watson Central

In this table if i write a sql
select ClRefNo,Surname+ ', ' + Forname as Name, Area from ClientRef
Group by Surname,Forename
Order by Surname
It wil display the result like this way
ClRefNo Name Area
1253 Noel,Watson Central
1254 Thomson,Moore Central
1254 Thomsan,Moore South

Now my problem is, i need to display a * sign when the ClRefNo get repeated ? like this way
ClRefNo Name Area
1253 Noel,Watson * Central
1254 Thomson,Moore * Central
1254 Thomsan,Moore South

how to do this task ?
regards
Suis

use the following query...

Code Snippet

Create Table #data (

[ClRefNo] int ,

[Surname] Varchar(100) ,

[Forname] Varchar(100) ,

[Area] Varchar(100)

);

Insert Into #data Values('1254','Thomson','Moore','Central');

Insert Into #data Values('1254','Thomsan','Moore','South');

Insert Into #data Values('1253','Noel','Watson','Central');

Select

Main.ClRefNo

,Main.Surname + ', ' + Forname + Case When CountData.Counter > 1 Then '*' Else '' End as Name

,Main.Area

From

#data Main

Join (Select ClRefNo, Count(ClRefNo) Counter From #data Group By ClRefNo) as CountData

On Countdata.ClRefNo = Main.ClRefNo

Order by

Surname

|||
HI Manivannan.D.Sekaran
thanks very much for r valuable comments
Ok that query is worling perfectly.
now i got another problem ,Eg: now my table looks like this

ClRefNo Surname Forname RefrlNo Area
1254 Thomson Moore 24 Central
1254 Thomsan Moore 24 South
1253 Noel Watson 12 Central
1253 Noel Watson 13 Central

So now i have got another column call RefrlNo
i need to check if ClRefno is the same and the RefrlNo is same then it should add * sign after the name ? looks like this way

ClRefNo Name Area
1253 Noel,Watson Central
1253 Noel,Watson Central
1254 Thomson,Moore * Central
1254 Thomsan,Moore * South

Any Idea ?|||

Suis,

It's very frustrating for us when someone dribbles out their problem a little bit at a time. It might be useful if you were to just put out the entire issue and allow the folks here to help you find a solution.

I do think that you 'should' be able to take Mani's suggestion and make a simple alteration and solve the issue yourselt. It is almost the identical problem. Have you even bothered to try that?

|||

here you go..

Code Snippet

Create Table #data (

[ClRefNo] int ,

[RefrlNo] int,

[Surname] Varchar(100) ,

[Forname] Varchar(100) ,

[Area] Varchar(100) ,

);

Insert Into #data Values('1254',1,'Thomson','Moore','Central');

Insert Into #data Values('1254',1,'Thomsan','Moore','South');

Insert Into #data Values('1253',1,'Noel','Watson','Central');

Insert Into #data Values('1253',2,'Noel','Watson','Central');

Select

Main.ClRefNo

,Main.Surname + ', ' + Forname + Case When CountData.Counter > 1 Then '*' Else '' End as Name

,Main.Area

From

#data Main

Join (Select ClRefNo,RefrlNo,Count(ClRefNo) Counter From #data Group By ClRefNo,RefrlNo) as CountData

On Countdata.ClRefNo = Main.ClRefNo And Countdata.RefrlNo = Main.RefrlNo

Order by

Surname

|||

Hi Arni,

Yes I tried My self like this way.now i got the output as i expected.

Select

Main.ClRefNo

,Main.Surname + ', ' + Forname + Case When CountData.Counter > 1 Then ' *' Else '' End as Name

,Main.Area

From

#data Main

Join (Select ClRefNo,Refrl_refno Count(ClRefNo) Counter From #data Group By ClRefNo,Refrl_refno) as CountData

On Countdata.ClRefNo = Main.ClRefNo and Countdata.Refrl_refno= #data..Refrl_refno

Order by

Surname

Thank you

regards

suis

|||

Great!

I'm glad that you were helped today, and expecially that you learned that you could take one idea and solve another problem with it. Excellent!