How to insert a space after each Manager Starts.

hi,
guys
i have query which given below output given below

manager personlevel person name
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil 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
20851 Howard Wilson1
20852Howard Wilson2
20853Howard Wilson3
20854Howard Wilson4
20855Howard Wilson5
null null null
60861Andrew Saxon
60862Andrew Saxon
60863Ian Thompson
60864Ian Thompson
60865Phil 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 MahindrooHi

You don't post the DDL or the current query so it is hard to know what your
SQL is.

Assuming something like:

SELECT Manager, Personlevel, PersonName from Mgmt

You could try (untested)

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
ORDER BY Id ASC, Manager DESC ) M

John

"Navin" <navinsm2@.rediffmail.com> wrote in message
news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> 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|||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
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil 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
> 20851 Howard Wilson1
> 20852Howard Wilson2
> 20853Howard Wilson3
> 20854Howard Wilson4
> 20855Howard Wilson5
> null null null
> 60861Andrew Saxon
> 60862Andrew Saxon
> 60863Ian Thompson
> 60864Ian Thompson
> 60865Phil 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|||Hi

Got around to testing it... you can't use the order by in the derived
table!

SELECT Manager, Personlevel, PersonName from
( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
UNION
SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
) M
ORDER BY id, Manager Desc

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f0009d7$0$18490$ed9e5944@.reading.news.pipex. net...
> Hi
> You don't post the DDL or the current query so it is hard to know what
your
> SQL is.
> Assuming something like:
> SELECT Manager, Personlevel, PersonName from Mgmt
> You could try (untested)
> SELECT Manager, Personlevel, PersonName from
> ( SELECT Manager as Id, Manager, Personlevel, PersonName from Mgmt
> UNION
> SELECT DISTINCT Manager, NULL, NULL, NULL from Mgmt
> ORDER BY Id ASC, Manager DESC ) M
> John
> "Navin" <navinsm2@.rediffmail.com> wrote in message
> news:5dc7f532.0306300051.7b6d1f67@.posting.google.c om...
> > 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