3 Replies Latest reply: Apr 22, 2013 11:43 PM by Manik RSS

    Sql Query

    1004399
      i wanna output as

      1 1 1

      1 1 2

      1 1 3

      1 2 1

      1 2 2

      1 2 3

      1 3 1

      1 3 2

      1 3 3

      Prepare SQL Query for above One.
        • 1. Re: Sql Query
          Manik
              SELECT 1 col1,
                     CEIL (ROWNUM / 3) col2,
                     ROW_NUMBER () OVER (PARTITION BY 1, CEIL (ROWNUM / 3) ORDER BY 1) col3
                FROM DUAL
          CONNECT BY ROWNUM <= 9;
          OUTPUT:
          COL1     COL2     COL3
          -------------------------------------------
          1     1     1
          1     1     2
          1     1     3
          1     2     1
          1     2     2
          1     2     3
          1     3     1
          1     3     2
          1     3     3
          You may concatenate it as well if you like..

          Cheers,
          Manik.
          • 2. Re: Sql Query
            Frank Kulash
            Hi,

            So, the first column in the result set is always 1, and the other columns are all possible combinations of 1, 2 and 3; is that it?
            If so:
            WITH     cntr     AS
            (
                 SELECT     LEVEL     AS n
                 FROM     dual
                 CONNECT BY     LEVEL     <= 3
            )
            SELECT     1     AS col_a
            ,     b.n     AS col_b
            ,     c.n     AS col_c
            FROM         cntr  b
            CROSS JOIN  cntr  c
            ;
            • 3. Re: Sql Query
              Manik
              Following your foot steps and if that is what user requires... then another way to solve this...

              :D
              SELECT 1 col1, a.* , b.*
                FROM TABLE (sys.odcinumberlist (1, 2, 3)) a,
                     TABLE (sys.odcinumberlist (1, 2, 3)) b;
              OUTPUT:
              COL1     COLUMN_VALUE     COLUMN_VALUE_1
              1     1     1
              1     1     2
              1     1     3
              1     2     1
              1     2     2
              1     2     3
              1     3     1
              1     3     2
              1     3     3
              Cheers,
              Manik.