Show one record for multi-line output

[JSMITH242B]

Hi Group,
Hi Group,
I have the following output:

Ac/No Client Turnover
29374 GSK - GlaxoSmithKline - UK 72829.00
29374 GSK - GlaxoSmithKline - USA 93838.00


The Customer wants to see this as ONE line - still showing the Clients' name. Is this possible?

Kind Regards

 

[johnherman]

Not clear what you want the output to look like. Will both Client names be displayed? either? will the turnover numbers be summed?

Please provide a sample output.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[nikhilparchure]

Yaa we need a sample input to analyse this.

Beware using direct DB values into such forums
eg : Client Name , Server Name ...
could land you into trouble.

Parchure Nikhil

 

[JSMITH242B]

Parchure,
Thanks for the tip.... those names were made up however.

The output I want is like:

Ac/No Client Name Turnover
29374 GSK - GlaxoSmithKline - UK sumofturnover for Ac/No

The problem is the Client Name. It varies even though it has the same Ac/No. It is how it has been input by the data entry clerk. A lot of this is historic data which is still being used.

What was suggested was that the **FIRST** Client Name the query came across for that Ac/No, it was used for all subsequent Client Names with the same Ac/No. Possible or not?

I was thinking along the lines of some sort of string manipulation on the Client Name - maybe the First 3 characters but if I group on this, it might be meaningless as in the above example, you will have GLA as the Client Name. Also, I was thinking if a Client only has a 2 letter name, will this cause the query to fall over....

More questions than answers.. I know!!

Kind Regards

 

[nikhilparchure]

Select Ac/No , max(Client), sum(Turnover)
From TableName
Group By Ac/No

Could This Help!!!

Parchure Nikhil

 

[nikhilparchure]

Declare @Client varchar(100)
Select @Client = Client
from TableName
Where A/C No = 29374
and timestamp = min(timestamp)

Select A/c No , @Client , sum(Amount)
From TableName
Group by A/C No


The above approach could be used to get the First client name used by the data entry operator.

Hope there is timestamp column in the Table

Parchure Nikhil.