12 Replies Latest reply: Feb 14, 2013 6:59 AM by user3206995 RSS

    Formating output

    user3206995
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Formating output
            SRN
            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
              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
                Billy~Verreynne
                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
                  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
                    Billy~Verreynne
                    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
                      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
                        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
                          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
                            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
                              Frank, I will get back to you on that.

                              cheers
                              • 12. Re: Formating output
                                user3206995
                                Frank, your SQL script worked.


                                Thanks a lot for your positive input.