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

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
> 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

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
>
>