How to insert a space after each Manager Starts.

[Navin]

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

[John Bell]

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" <na******@rediffmail.com> wrote in message
news:5d**************************@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

[AMIT]

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.



na******@rediffmail.com (Navin) wrote in message news:<5d**************************@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

[John Bell]

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" <jb************@hotmail.com> wrote in message
news:3f***********************@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" <na******@rediffmail.com> wrote in message
news:5d**************************@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