Select Statement...

[Omavlana]

Hi

I have written this query for generating a report.


select * from shrcertmaster left outer join
shrsharemaster on
shmacno = ctmacno


This returns the output given below.

CtmCoy CtmAcNo CtmCertNo CtmBenf CtmBatch CtmShare
UB 1234567905 123453 123476 0001 1000
UB 1234567905 123466 123476 0001 1000
UB 1234567905 123479 123476 0002 10000
UB 1234567905 1234891 123476 0002 15000


I don't want the 2nd column CtmAcno "1234567905 " to be repeated if it
is same CtmAcNo. Instead it should display null.

How can I modify the above query? Pls help..

Regards.

[Erland Sommarskog]

Re: Select Statement...

Omavlana (kiran@boardroo mlimited.com) writes:[color=blue]
> I have written this query for generating a report.
>
>
> select * from shrcertmaster left outer join
> shrsharemaster on
> shmacno = ctmacno
>
>
> This returns the output given below.
>
> CtmCoy CtmAcNo CtmCertNo CtmBenf CtmBatch CtmShare
> UB 1234567905 123453 123476 0001 1000
> UB 1234567905 123466 123476 0001 1000
> UB 1234567905 123479 123476 0002 10000
> UB 1234567905 1234891 123476 0002 15000
>
>
> I don't want the 2nd column CtmAcno "1234567905 " to be repeated if it
> is same CtmAcNo. Instead it should display null.[/color]

First, for this to make any sense what so ever, you must have an ORDER BY
clause in your SELECT statement.

This is possible to achieve by stashing the data into a temp table with
an IDENTITY column. Then you self-join the temp table, on "id = id - 1",
and you set the CtmAcNo to NULL for all columns, save those the values
are different in the two instances of the table.

However, this kind of thing does not really belong in SQL Server. A
database engine is about supplying data. Not format it for a report.
Report generators typically have built-in support for this kind of
thing. Even if you don't have a report generator, but only some simple
client, it's still simpler to do this client-side.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp