6 Replies Latest reply on May 28, 2020 1:40 PM by Jorgelina

    Using SQL - get data in specific columns

    Jorgelina

      Hi,

       

      Sorry if the subject is not correct, but I am not sure how is the best way to sum up the issue I am having.

      I have the following data (that is already coming from another  select sentence):

       

      A                              B                                       C

      TYPE1                    ZZA                                   20.50

      TYPE1                    ZZB                                   32.00

      TYPE1                    ZZC                                  35.25

      TYPE1                    ZZB                                   38.00

      TYPE1                    ZZD                                   40.00

      TYPE2                    XXA                                   16.00

      TYPE2                    XXB                                   17.00

      TYPE2                    XXB                                    22.00

       

      For each TYPE1 and TYPE2 the amount of rows can vary. (There could also be a TYPE3, 4, etc; but I'd rather try to focus the issue in just 2, though TYPE1/TYPE2, is also a variable name).

      What I need to produce is:

       

      TYPE1                                                   TYPE2

      ZZA - 20.50                                            XXA - 16.00

      ZZB - 32.00                                            XXB - 17.00

      ZZC - 35.25                                            XXB - 22.00

      ZZB - 38.00

      ZZD - 40.00

       

      Is this possible just with an sql query?

      Is the only choice I have to create something similar to a dynamic query with lots of "select  ZZA-20.50 type1, XXA-16.00 type2  from dual      union all     select ZZB-32.00 type1,  XXB-17.00 type2   union all .... " and so on?

      I would rather not, since, there is more data than just 2 columns, and the place where I would be returning the dynamic query to then be executed, cannot hold more than just 4000 characters (a limitation on APEX for what I am trying to achieve).

      Maybe someone can hint me on the right direction on how to solve this?

       

      Thanks!

        • 1. Re: Using SQL - get data in specific columns
          Frank Kulash

          Hi,

           

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          Jorgelina wrote:

           

          Hi,

           

          Sorry if the subject is not correct, but I am not sure how is the best way to sum up the issue I am having.

          I have the following data (that is already coming from another select sentence):

           

          A B C

          TYPE1 ZZA 20.50

          TYPE1 ZZB 32.00

          TYPE1 ZZC 35.25

          TYPE1 ZZB 38.00

          TYPE1 ZZD 40.00

          TYPE2 XXA 16.00

          TYPE2 XXB 17.00

          TYPE2 XXB 22.00

           

          For each TYPE1 and TYPE2 the amount of rows can vary. (There could also be a TYPE3, 4, etc; but I'd rather try to focus the issue in just 2, though TYPE1/TYPE2, is also a variable name).

          What I need to produce is:

           

          TYPE1 TYPE2

          ZZA - 20.50 XXA - 16.00

          ZZB - 32.00 XXB - 17.00

          ZZC - 35.25 XXB - 22.00

          ZZB - 38.00

          ZZD - 40.00

           

          Is this possible just with an sql query?

          Is the only choice I have to create something similar to a dynamic query with lots of "select ZZA-20.50 type1, XXA-16.00 type2 from dual union all select ZZB-32.00 type1, XXB-17.00 type2 union all .... " and so on?

          I would rather not, since, there is more data than just 2 columns, and the place where I would be returning the dynamic query to then be executed, cannot hold more than just 4000 characters (a limitation on APEX for what I am trying to achieve).

          Maybe someone can hint me on the right direction on how to solve this?

           

          Thanks!

          So, from the given data, the desired output is 5 rows with 4 columns.  (Some of the columns will be NULL towards the end of the result set.)  Bot for different sample data, you might need a different number of rows, and a different number of columns.  Is that right?

           

          In SQL, the exact of columns must be hard-coded into the query  If you want the number of columns to depend on the data actually in the table, then you need dynamic SQL.  Apex may have tools to help in creating dynamic SQL like this.  Ask in the Apex Forum: Oracle Application Express (APEX)

           

          Do you really need a variable number of columns?  For example, would you be satisfied with an XML table, that's one big XMLTYPE column in SQL, but can be displayed as a variable number of columns?  Or how about a VARCHAR2 (4000) column that was formatted so that a person reading it thought there were separate columns?  Both are possible using SELECT … PIVOT in static (that is, NOT dynamic) SQL.

           

          Another possible work-around in pure SQL is to have a fixed number of columns, and only populate the ones you need.  For example, if you know you'll never have more than 30 distinct values for column a, then you could write a static query that always produced 60 columns of output.  If you only need 4 columns (as in the example you gave), then the last 56 columns would be NULL on all rows.  (If you need more than 60 columns, how is anybody going to read the output?)

           

          Is the combination of columns a and b unique?  If not, include examples when you post the sample data, desired results and explanation.

          • 2. Re: Using SQL - get data in specific columns
            Frank Kulash

            Hi,

             

            See PIVOT SQL

            for an example of how to format a single VARCHAR2 column so that it looks like a variable number of columns.

            • 3. Re: Using SQL - get data in specific columns
              odie_63

              Jorgelina wrote:

               

              Is this possible just with an sql query?

              I think this type of "columnar" presentation should typically be generated by a reporting tool, not the SQL engine.

               

              Frank has summarized the options you have if you must use SQL.

              The key point is that the number of resulting columns must be known at parse time.

               

              With a fixed number of columns (say two, as in your example), one way is to perform a PIVOT operation, with a little bit of preprocessing.

              For the following, I assumed C is numeric and is used to sort the result set in ascending order within each group :

               

              with tmp (A, B, C) as (

                select 'TYPE1', 'ZZA', 20.50 from dual union all

                select 'TYPE1', 'ZZB', 32.00 from dual union all

                select 'TYPE1', 'ZZC', 35.25 from dual union all

                select 'TYPE1', 'ZZB', 38.00 from dual union all

                select 'TYPE1', 'ZZD', 40.00 from dual union all

                select 'TYPE2', 'XXA', 16.00 from dual union all

                select 'TYPE2', 'XXB', 17.00 from dual union all

                select 'TYPE2', 'XXB', 22.00 from dual

              )

              select type1, type2

              from (

                select a

                     , b || '-' || to_char(c,'FM99990.90') as bc

                     , row_number() over(partition by a order by c) rn

                from tmp

              )

              pivot (

                min(bc) for a in ('TYPE1' as type1, 'TYPE2' as type2)

              )

              order by rn

              ;

               

              TYPE1         TYPE2

              ------------- -------------

              ZZA-20.50     XXA-16.00

              ZZB-32.00     XXB-17.00

              ZZC-35.25     XXB-22.00

              ZZB-38.00    

              ZZD-40.00    

               

              The parts in red are the "moving" parts.

              If I remember correctly, you can use substitution variables in APEX, so maybe an option is to build those lists of columns separately and embed them directly in the query string.

              1 person found this helpful
              • 4. Re: Using SQL - get data in specific columns
                Jorgelina

                Hi,

                 

                Thanks all.

                Odie you proivded a working example =)

                I was doubtful about pivoting since it always needs an aggregate function, but the way you have set it up, I think can work in the more complex query I already have.
                I will try this and let you know!!

                 

                Thanks!!

                • 5. Re: Using SQL - get data in specific columns
                  Frank Kulash

                  Hi,

                  Jorgelina wrote:

                  ...

                  I was doubtful about pivoting since it always needs an aggregate function, but the way you have set it up, I think can work in the more complex query I already have.

                  ...

                  Right; pivoting always requires an aggregate function.  If there's a one-to-one correspondence between unpivoted values and values in the output (which is often the case in pivots) then that aggregate function can be MIN (which Odie used in reply #3) or MAX.

                  • 6. Re: Using SQL - get data in specific columns
                    Jorgelina

                    Thanks a lot!!

                    Tested it, added some more info I needed and works wonderfuly!! =)