Professional Web Applications Themes

How to insert a space after each Manager Starts. - Microsoft SQL / MS SQL Server

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 that output looks like this manager personlevel person name 2085 1 Howard Wilson1 ...

  1. #1

    Default How to insert a space after each Manager Starts.

    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 that 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
    Navin Guest

  2. #2

    Default How to insert a space after each Manager Starts.

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

  3. #3

    Default Re: How to insert a space after each Manager Starts.

    [email]navinsm2rediffmail.com[/email] (Navin) wrote in message news:<5dc7f532.0306300050.2913b7a2posting.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 that 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
    In sql*plus

    break on manager nodup skip 2

    Sybrand Bakker
    Senior Oracle DBA
    sybrandb@yahoo.com Guest

  4. #4

    Default Re: How to insert a space after each Manager Starts.

    [email]navinsm2rediffmail.com[/email] (Navin) wrote in message news:<5dc7f532.0306300050.2913b7a2posting.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 that 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
    Hello Navin,

    If I'm interpreting what you want to do correctly, I think the easiest
    way is to use the SQLPlus "BREAK" command. This takes the form:

    BREAK ON <Column alias> <Action>

    and what it does is to look at the values in the column defined by
    <Column alias> and whenever that value changes it performs the action
    defined by <Action>. Thus you could say:

    BREAK ON manager SKIP 2

    and every time the value in column manager changes the query output
    includes a couple of blank lines. The default action with the BREAK
    command is that the value of the column you BREAK on is only printed
    each time the value changes (or at the top of a new page) but if you
    specifically want the value to be printed for every line add the
    keyword DUPLICATES (or DUP).

    It probably hardly needs saying that to get a sensible output you need
    to ORDER BY the column(s) you BREAK on!

    Hope that helps.

    Regards,

    Tim Kearsley
    Database Manager
    Milton Keynes Council
    Tim Kearsley Guest

  5. #5

    Default Re: How to insert a space after each Manager Starts.

    To display a blank line after each change of manager, use "break on
    manager skip 1" before running your report in SQL*Plus. Note that this
    eliminates the display of the same manager number when multiple
    records have the same manager number. It is only displayed for the
    first record.

    Hope that this is what you wanted to know.

    Daniel
    > 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 that 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
    Daniel Roy Guest

  6. #6

    Default Re: How to insert a space after each Manager Starts.

    hi,
    Guys this not what i want ...
    i do want to run in sql&plus or report or use break command
    i wnat to get the output using normal sql and return into my front end
    ....
    i wnat to return the output using normal sql and return back to my
    front end that asp
    regds,


    navin
    Jeremy <newspostingshazelweb.co.uk> wrote in message news:<MPG.196a782df32c5cf3989781news.cis.dfn.de>. ..
    > In article <3722db.0306300833.1104942posting.google.com>,
    > [email]danielroy10junkhotmail.com[/email] says...
    > > To display a blank line after each change of manager, use "break on
    > > manager skip 1" before running your report in SQL*Plus. Note that this
    > > eliminates the display of the same manager number when multiple
    > > records have the same manager number. It is only displayed for the
    > > first record.
    > >
    > Seems like we're all agreed on the solution then - that must be 5 or 6
    > virtually identical posts...
    >
    > cheers
    Navin Guest

  7. #7

    Default Re: How to insert a space after each Manager Starts.

    [email]navinsm2rediffmail.com[/email] (Navin) wrote in message news:<5dc7f532.0306302040.575f3f0posting.google.c om>...
    > hi,
    > Guys this not what i want ...
    > i do want to run in sql&plus or report or use break command
    > i wnat to get the output using normal sql and return into my front end
    > ...
    > i wnat to return the output using normal sql and return back to my
    > front end that asp
    > regds,
    >
    >
    > navin

    Not possible. PERIOD.

    Just the right tool for the right job. SQL is NOT a REPORTING language.
    What's wrong with a little bit of code, or using the dbms_http package?


    Sybrand Bakker
    Senior Oracle DBA
    sybrandb@yahoo.com Guest

  8. #8

    Default Re: How to insert a space after each Manager Starts.

    CREATE TABLE t (id number, name VARCHAR2(100);
    /
    -- create some test data
    BEGIN
    FOR i IN 1 .. 5 LOOP
    FOR ii IN 1 .. 10 LOOP
    INSERT INTO T VALUES (i,'Name '||ii);
    END LOOP;
    END LOOP;
    END;

    -- and your query is (in Oracle 8i and up only):

    SELECT DECODE(name_group,0,ID,NULL) ID,NAME
    FROM (

    SELECT ID, NAME,GROUPING(ID) id_group, GROUPING(NAME) name_group
    FROM t
    GROUP BY ROLLUP (ID,NAME))
    WHERE id_group=0;

    Jan
    Jan Guest

  9. #9

    Default Re: How to insert a space after each Manager Starts.

    You are probably asking too much of SQL - remember that it is a
    database querying language not a formatting language.

    How about getting your front-end app to take the unformatted data and
    present it differently?

    ETA

    [email]navinsm2rediffmail.com[/email] (Navin) wrote in message news:<5dc7f532.0306302040.575f3f0posting.google.c om>...
    > hi,
    > Guys this not what i want ...
    > i do want to run in sql&plus or report or use break command
    > i wnat to get the output using normal sql and return into my front end
    > ...
    > i wnat to return the output using normal sql and return back to my
    > front end that asp
    > regds,
    >
    >
    > navin
    > Jeremy <newspostingshazelweb.co.uk> wrote in message news:<MPG.196a782df32c5cf3989781news.cis.dfn.de>. ..
    > > In article <3722db.0306300833.1104942posting.google.com>,
    > > [email]danielroy10junkhotmail.com[/email] says...
    > > > To display a blank line after each change of manager, use "break on
    > > > manager skip 1" before running your report in SQL*Plus. Note that this
    > > > eliminates the display of the same manager number when multiple
    > > > records have the same manager number. It is only displayed for the
    > > > first record.
    > > >
    > > Seems like we're all agreed on the solution then - that must be 5 or 6
    > > virtually identical posts...
    > >
    > > cheers
    Ethel Aardvark Guest

  10. #10

    Default Re: How to insert a space after each Manager Starts.

    [email]navinsm2rediffmail.com[/email] (Navin) wrote in message news:<5dc7f532.0306302040.575f3f0posting.google.c om>...
    > hi,
    > Guys this not what i want ...
    > i do want to run in sql&plus or report or use break command
    > i wnat to get the output using normal sql and return into my front end
    > ...
    > i wnat to return the output using normal sql and return back to my
    > front end that asp
    > regds,
    >
    >
    > navin
    > Jeremy <newspostingshazelweb.co.uk> wrote in message news:<MPG.196a782df32c5cf3989781news.cis.dfn.de>. ..
    > > In article <3722db.0306300833.1104942posting.google.com>,
    > > [email]danielroy10junkhotmail.com[/email] says...
    > > > To display a blank line after each change of manager, use "break on
    > > > manager skip 1" before running your report in SQL*Plus. Note that this
    > > > eliminates the display of the same manager number when multiple
    > > > records have the same manager number. It is only displayed for the
    > > > first record.
    > > >
    > > Seems like we're all agreed on the solution then - that must be 5 or 6
    > > virtually identical posts...
    > >
    > > cheers
    Navin,

    What you are talking about is _formatting_ in your ASP. Just loop
    through your ADO recordset sorted by manager, check the manager value,
    and put a <TR></TR>, or however you are formatting blank lines, if it
    changes.

    Regards,

    Steve
    Steve Howard Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. Audio starts on click/stops/and finaly starts correctwith video
    By N steht f?r? in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: December 19th, 02:30 PM
  3. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  4. Replies: 3
    Last Post: September 30th, 09:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139