1 2 Previous Next 23 Replies Latest reply on Oct 21, 2010 2:32 PM by 570874

    Row to Column display in PL/SQL

    San
      Hi All,

      I have dyanamic query its display the periods.

      ex: if the uesr gives the input like APR-07 to APR 08.

      It will display the record like
      APR-10   1200
      MAR-10   1400
      -
      -
      -
      -
      -
      APR-08 2500
      Now i want to display the above code as
      APR-10 MAR-10 - - - - - -- - -  APR-08
      1200    1400                      2500
      but the column name is dynamic its not a static.


      Thanks in Advance.

      Cheers,
      Shan
        • 1. Re: Row to Column display in PL/SQL
          635471
          Did you try Google first?
          • 2. Re: Row to Column display in PL/SQL
            787331
            Search for Pivot

            Regards
            Ved
            • 3. Re: Row to Column display in PL/SQL
              San
              Hi 784328 ,

              Pivot table we can give only static values for column right??

              cheers,
              Shan
              • 4. Re: Row to Column display in PL/SQL
                BluShadow
                Shan wrote:
                Hi 784328 ,

                Pivot table we can give only static values for column right??

                cheers,
                Shan
                Any SQL must have a pre-determined number of columns in the output before any data is fetched, so you cannot easily base the number of output columns on the data itself.

                One method is to use dynamic SQL to query the data first, then build up a dynamic query from that which returns the data pivoted as you require.
                Another method is to interface with Oracle's ODCI interface e.g. How to pipeline a function with a dynamic number of columns?

                Of course, the most correct method is to use the right tool for the right job.
                In this case, what you're asking for is something that is more easily achieved by a reporting tool, as these query the data first and then format the report based on the data. SQL cannot naturally do this itself.
                1 person found this helpful
                • 5. Re: Row to Column display in PL/SQL
                  Frank Kulash
                  Hi, Shan,

                  As Blushadow said, this is a display problem. Display problems are usaully hnadled best by the front end, not SQL or PL/SQL.

                  If you do need to do this in SQL or PL/SQL, you have use Dynamic SQL to produce a variable number of columns. You might be better off producing a fixed number of columns. Using String Aggregation you can make a big string that is the concatenation of a variable number of small strings, such as the two strings below:
                  APR-10      MAR-10     ...     APR-08
                  1200    1400    ...     2500
                  See the following thread for more details and more options:
                  Re: Report count and sum from many rows into many columns
                  1 person found this helpful
                  • 6. Re: Row to Column display in PL/SQL
                    782162
                    Hi Frank, can you help me on upgrading DB from Oracle 9i to 11g on windows OS , i m struck with it from last 2 weeks..
                    • 7. Re: Row to Column display in PL/SQL
                      Saubhik
                      I think Frank can help, but he needs your address, plane fare and hourly charges ;)
                      • 8. Re: Row to Column display in PL/SQL
                        BluShadow
                        user13344072 wrote:
                        Hi Frank, can you help me on upgrading DB from Oracle 9i to 11g on windows OS , i m struck with it from last 2 weeks..
                        Hijacking a thread, posting a question not related to SQL or PL/SQL and asking someone specific for help who no doubt has their own job to do. How rude can you get?

                        Aside from that, saying "I'm stuck" tells people nothing.

                        My answer would be, read the feckin' upgrade notes and follow them. One of the first things it will tell you to do is backup your database, so then you can try and retry until you get it right (either that or hire a DBA to do it for you)
                        • 9. Re: Row to Column display in PL/SQL
                          tortureduck
                          Why not use wm_concat (10g) or LISTAGG(11g)?

                          I guess you dont really need the column name its even fine if you just display this two rows from your example...

                          SELECT WM_CONCAT(PERIODS) AS PERIODS FROM MYTESTTAB;
                          SELECT WM_CONCAT(VALS) AS VALS FROM MYTESTTAB;
                          • 10. Re: Row to Column display in PL/SQL
                            BluShadow
                            tortureduck wrote:
                            Why not use wm_concat (10g) or LISTAGG(11g)?

                            I guess you dont really need the column name its even fine if you just display this two rows from your example...

                            SELECT WM_CONCAT(PERIODS) AS PERIODS FROM MYTESTTAB;
                            SELECT WM_CONCAT(VALS) AS VALS FROM MYTESTTAB;
                            WM_CONCAT is only available if Workflow is installed. It's also an undocumented, and therefore unsupported, function, so if you use it in production code, Oracle will not guarantee support of your database.
                            • 11. Re: Row to Column display in PL/SQL
                              tortureduck
                              @Shan A lot of people are using it, search in this forum for example, if you are on 11g use listagg as mentioned.

                              @Blu
                              BluShadow wrote:
                              WM_CONCAT is only available if Workflow is installed. It's also an undocumented, and therefore unsupported, function, so if you use it in production code, Oracle will not guarantee support of your database.
                              Really?
                              Means if i use some kind of function that does not work as it should Oracle can say "you were a bad boy and did not read the manual - so your support is gone" ?
                              Or don't they just not support this function?
                              • 12. Re: Row to Column display in PL/SQL
                                6363
                                I have questioned this before.

                                Re: Let us discussion at wmsys.wm_concat VS ListAgg

                                And noted some of the flaws of this conclusion.

                                Re: Let us discussion at wmsys.wm_concat VS ListAgg

                                I agree that using anything that is not documented should be approached with caution if at all, but reaching the conclusion that Oracle will not support your database just appears to be scaremongering.

                                It gets worse when a useful new feature is not documented due to an oversight in the documentation which has happened before, or if the documentation is ambiguous such as in the case of the connect by row generator technique.
                                • 13. Re: Row to Column display in PL/SQL
                                  BluShadow
                                  tortureduck wrote:
                                  @Blu
                                  BluShadow wrote:
                                  WM_CONCAT is only available if Workflow is installed. It's also an undocumented, and therefore unsupported, function, so if you use it in production code, Oracle will not guarantee support of your database.
                                  Really?
                                  Means if i use some kind of function that does not work as it should Oracle can say "you were a bad boy and did not read the manual - so your support is gone" ?
                                  Or don't they just not support this function?
                                  Means, you use the function, database is upgraded to latest function, your code no longer works, you raise issue with Oracle, they see you've used undocumented function and go tell you to update all your code to remove it/replace it and then if you still have a problem they'll look at it. As it's undocumented, you can't guarantee that Oracle won't change how it works in future versions of the database. Documented functions have known and documented functionality, so that should be retained throughout future versions.

                                  ;)
                                  • 14. Re: Row to Column display in PL/SQL
                                    tortureduck
                                    BluShadow wrote:
                                    Means, you use the function, database is upgraded to latest function, your code no longer works, you raise issue with Oracle, they see you've used undocumented function and go tell you to update all your code to remove it/replace it and then if you still have a problem they'll look at it.
                                    As it's undocumented, you can't guarantee that Oracle won't change how it works in future versions of the database. Documented functions have known and documented functionality, so that should be retained throughout future versions.
                                    No difference to supported functions then :-))
                                    I agree they look at it. Does not mean they fix it...

                                    ;-)
                                    1 2 Previous Next