This discussion is archived
12 Replies Latest reply: Feb 14, 2013 4:59 AM by user3206995 RSS

Formating output

user3206995 Newbie
Currently Being Moderated
Hi All

I have a query similar to :

select race_version_spec AS GAME TYPE, count(*) AS Participants
from table B
where xx etc
group by race_version_spec

my output is as follows:

Game Type participants
hurdles 5
hurdles 5
200 metres 10
200 metres 10
800 Metres 7
400 Metres 6
400 Metres 6

I would like the ouptut to be something like this?

Hurdes=5, 200 Metres=10,800, Metres=7, 400 Metres=6

and when zero rows are returned something like,

recorded races=0
I have an idea of using the case function for that part

tips on how to pproach this is verz much appreciated.


Thanks in advance for your feedback
  • 1. Re: Formating output
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Formating output
    SRN Newbie
    Currently Being Moderated
    a union query should be enough..since your table is having records, there wont occur a zero race scenario right?
    please be more specific regarding your problem/question..

    Regards,
    Sandeep
  • 3. Re: Formating output
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As Blushadow said, you really need to post some sample data, the exact results you want from that data, and an explanation of how you get those results from that data.
    user3206995 wrote:
    Hi All

    I have a query similar to :

    select race_version_spec AS GAME TYPE, count(*) AS Participants
    from table B
    where xx etc
    group by race_version_spec

    my output is as follows:

    Game Type participants
    hurdles 5
    hurdles 5
    200 metres 10
    200 metres 10
    800 Metres 7
    400 Metres 6
    400 Metres 6
    So there are 2 different that both have race_version_specs that look like 'hurdles'. What is different between them? Does one (or both) of them have trailing spaces, or something like that?
    I would like the ouptut to be something like this?

    Hurdes=5, 200 Metres=10,800, Metres=7, 400 Metres=6
    So you only want 1 entry for 'Hurdles', even though there are 2 distinct values that look like 'hurdles'; is that it? Depending on what distinguishes the 2 strings, you can use RTRIM or some similar function to make them the same. Use INITCAP to capitalize the 'h' in 'hurdles' and the 'm' in '200 metres'.
    What results would you want if the 2 'hurdles groups did not have the same number of participants?

    Is the order of items in the ','-delimited list important? If so, how do you determine that, say '800 metres' comes after '200 metres' but before '400 metres'?
    and when zero rows are returned something like,

    recorded races=0
    I have an idea of using the case function for that part
    CASE would work, but NVL or COALESCE would do just as well, and they're a little simpler.
    tips on how to pproach this is verz much appreciated.

    Thanks in advance for your feedback
    This should give you some ideas:
    WITH     got_str          AS
    (
         SELECT       job
         ,       job || '='
                    || TO_CHAR (COUNT (*))
                    || ','               AS str
         ,       ROW_NUMBER () 
                    OVER (ORDER BY job)     AS r_num
         FROM      scott.emp
         WHERE       deptno     < 30
         GROUP BY  job
    )
    SELECT       NVL ( RTRIM ( XMLAGG ( XMLELEMENT (e, str)
                                    ORDER BY  job
                                   ).EXTRACT ('//text()')
                       , ','
                    )
               , 'Recorded jobs=0'
               )          AS job_list
    FROM      got_str
    ;
    Output:
    JOB_LIST
    ------------------------------------------
    ANALYST=2,CLERK=3,MANAGER=2,PRESIDENT=1
    But if we change the condition in the sub-query to
    WHERE       deptno     < 0
    so that the sub-query produces no rows, then the output is
    JOB_LIST
    ----------------------------------------
    Recorded jobs=0
  • 4. Re: Formating output
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user3206995 wrote:

    I would like the ouptut to be something like this?
    Hurdes=5, 200 Metres=10,800, Metres=7, 400 Metres=6
    That is done in the software layer that is responsible for the rendering of server data. In other words, that is done in the client. And NOT in SQL and NOT in the server.

    SQL produces output. The client formats and renders the output. A very basic, and fundamental, client-server concept.

    A concept that seems to be often ignored around here....

    Question is whether you are going to stop, grasp and understand this fundamental concept?
  • 5. Re: Formating output
    user3206995 Newbie
    Currently Being Moderated
    yes, you are correct Billy, but due to lack of experience with such task, I thought it best to ask questions.

    I know it is possible to format the result outside of the SQL commands.

    Thanks for your input.
  • 6. Re: Formating output
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user3206995 wrote:
    yes, you are correct Billy, but due to lack of experience with such task, I thought it best to ask questions.
    Well, one can use SQL to create ANSI art. That is not to say that SQL is ANSI art design software.

    Likewise, SQL can format structured data and output that as unstructured rendered text. That is not to say that SQL is a client report and rendering software.

    It has nothing to with a lack of experience. It has everything to do with understanding client-server basics, and using the SQL language for the purpose it was designed for, in client-server architecture.
  • 7. Re: Formating output
    ranit B Expert
    Currently Being Moderated
    my output is as follows:

    Game Type participants
    hurdles 5
    hurdles 5
    200 metres 10
    200 metres 10
    800 Metres 7
    400 Metres 6
    400 Metres 6

    I would like the ouptut to be something like this?

    Hurdes=5, 200 Metres=10,800, Metres=7, 400 Metres=6

    and when zero rows are returned something like,

    recorded races=0
    Not tested
    with x1 as(
    select
     game_type gt, partis, row_number() over(order by game_type,partis) rn
    from
     xx
    ),
    x2 as(
    select gametype||'='||partis gt
    from x1
    where
     rn = 1
    )
    select rtrim(XMLAgg(XMLElement(e, gt||',')).extract('//text()'),',') from x2;
    But, please explain what do you mean by zero rows returned?
    Please show some examples...
  • 8. Re: Formating output
    user3206995 Newbie
    Currently Being Moderated
    Billy,

    Yes, I fully understand your comments, as you state it is possible but not the normal approach.

    Thanks for your input.
  • 9. Re: Formating output
    user3206995 Newbie
    Currently Being Moderated
    here is a more precise detail:
    select substr(t.vol_race_xml_spec_version,1,length(t.vol_race_xml_spec_version)-1) AS RACE TYPE,count(*)
    OVER(PARTITION BY t.vol_race_xml_spec_version) AS NUM of participants
    from vol_race_history t
    where vol_race_STATE in (2,3)
    and TRUNC(sysdate) - TRUNC(t.vol_race_DATE) <=90
    and t.vol_participant_id > 1
    and t.vol_id > 1
    group by t.vol_race_xml_spec_version,t.vol_participant_id;

    RACE TYPE NUM of Particpants
    -------------------- ----------
    swimming 1
    Hurdles 5
    Hurdles 5
    Hurdles 5
    Hurdles 5
    Hurdles 5
    200 Metres 3
    200 Metres 3
    200 Metres 3
    800 Metres 3
    800 Metres 3
    800 Metres 3


    The multiple outputs are due to multiple runners .
    I am trying to acheive the following:

    Hurdles=5, 200 Metres=3, 800 Metres=3

    Thanks again for any feedback
  • 10. Re: Formating output
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    What's wrong with the query I posted yesterday? {message:id=10850094}

    Unless you post CREATE TABLE and INSERT statements for your data, I can't test it on your table. Adapting it for your table is just a matter of changing the column and table names.
    Post your adaptation of that query. Point out where it is producing the wrong results, and explain how you get the right results in those places.
  • 11. Re: Formating output
    user3206995 Newbie
    Currently Being Moderated
    Frank, I will get back to you on that.

    cheers
  • 12. Re: Formating output
    user3206995 Newbie
    Currently Being Moderated
    Frank, your SQL script worked.


    Thanks a lot for your positive input.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points