Hi Navin M,

Same other way round.


SELECT 'N' 'GRP_SEP',manager, personlevel ,[person name] FROM
TableName
UNION ALL
SELECT DISTINCT 'Y',manager,NULL,NULL FROM TableName
ORDER BY manager,GRP_SEP ASC

Group seperator is added to explicitly know that row with 'Y' is group
seperator and avoid null conflit if personlevel and name both are
null.

Also note that Manager field has appropriate index on it.

hope this helps you.

Thanks Amit.











navinsm2@rediffmail.com (Navin) wrote in message news:<5dc7f532.0306300051.7b6d1f67@posting.google. com>...
> hi,
> guys
> i have query which given below output given below
>
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
>
> what i want is after a manager ends i want a null to be inserted for
> each of there columns
> so that i can distinguish that when a new manager starts
>
> so thatt output looks like this
>
> manager personlevel person name
> 2085 1 Howard Wilson1
> 2085 2 Howard Wilson2
> 2085 3 Howard Wilson3
> 2085 4 Howard Wilson4
> 2085 5 Howard Wilson5
> null null null
> 6086 1 Andrew Saxon
> 6086 2 Andrew Saxon
> 6086 3 Ian Thompson
> 6086 4 Ian Thompson
> 6086 5 Phil Dargan
>
> Brlliant minds any solution for this..
> i know can i loop through the records and do it
> and check for a new manager
> but i want a better solution ..
> give me your ideads folks..
>
> Regards,
> Navin Mahindroo