6 Replies Latest reply: Jan 2, 2013 10:41 AM by ranit B RSS

    SQL Query

    Bilal
      Hi All,

      I have the following data in col column of table t:

      X
      Y
      Z

      I want to retrieve all the possible combinations of these values like:
      X
      XY
      XYZ
      Y
      YX
      YXZ
      Z
      ZX
      ZXY

      I wrote the following SQL:
      SELECT a.col||','||b.col FROM t a CROSS JOIN t b;
      but the result is only giving like:
      X,X
      X,Y
      X,Z
      Y,X
      Y,Y
      Z,Z
      Z,X
      Z,Y
      Z,Z

      Can anyone help me to re-write this query?

      The SQL commands to create table and populate dummy data is as below:
      CREATE TABLE t
      (col VARCHAR2(1));

      INSERT INTO t VALUES('X');
      INSERT INTO t VALUES('Y');
      INSERT INTO t VALUES('Z');

      Commit;

      Thanks in advance.
      Regards
      Bilal
        • 1. Re: SQL Query
          ranit B
          Hi Bilal,

          I hope (and a little sure) that there is some better way than this...
          Please try this -
          with xx as(
              select 'x' col from dual UNION ALL
              select 'y' col from dual UNION ALL
              select 'z' col from dual 
          ),
          x1 as (
          select col from xx
          UNION
          select col from (
              select a.col||b.col col from xx a, xx b
          )
          where
              substr(col,1,1) != substr(col,2,1)
          )
          select col from 
          ( select col, rank() over(partition by substr(col,1,1) order by col) rn
          from x1 )
          where rn !=3
          UNION
          select c.col||d.col from (
              select col, rank() over(partition by substr(col,1,1) order by col) rn from x1) c, xx d
          where
              rn <3
              and d.col != substr(c.col,1,1)
              and d.col != substr(c.col,2,1);
          gives
          x
          xy
          xyz
          y
          yx
          yxz
          z
          zx
          zxy
          I'll try for something efficient,,,

          Ranit B.


          Edited by: ranit B on Jan 2, 2013 4:17 PM
          -- code modified...
          • 2. Re: SQL Query
            AravindhK
            Hi Bilal,

            Please check the below query,

            SELECT T.COL FROM TEMP AS T
            UNION ALL
            SELECT T.COL||T1.COL FROM temp AS T JOIN TEMP AS T1 ON T.COL <> T1.COL
            UNION ALL
            SELECT T.COL||T1.COL||T2.COL FROM temp AS T JOIN TEMP AS T1 ON T.COL <> T1.COL JOIN temp AS T2 ON T1.COL <> T2.COL

            I did not get the results what you have expected. But I got all combination of data.

            COL

            X
            Y
            Z
            XY
            XZ
            YX
            YZ
            ZX
            ZY
            XYX
            XYZ
            XZX
            XZY
            YXY
            YXZ
            YZX
            YZY
            ZXY
            ZXZ
            ZYX
            ZYZ

            Thanks,
            Aravindh K

            Edited by: AravindhK on Jan 2, 2013 4:28 PM
            • 3. Re: SQL Query
              856859
              Hi:

              I guess you are looking for this one:

              SELECT A.COL_A from tbl_prac A
              UNION SELECT B.COL_A || B.COL_B from tbl_prac B

              Table: tbl_prac

              COL_A|COL_B
              -------------------
              X|XL
              Y|YZH
              Z|ZH
              Y|M

              Output:
              COL_A
              ---------
              X
              XXL
              Y
              YM
              YYZH
              Z
              ZZH

              Thanks/Tanvir
              • 4. Re: SQL Query
                Frank Kulash
                Hi, Bilal
                Bilal wrote:
                Hi All,

                I have the following data in col column of table t:

                X
                Y
                Z

                I want to retrieve all the possible combinations of these values like:
                X
                XY
                XYZ
                Y
                YX
                YXZ
                Z
                ZX
                ZXY
                'XYZ', 'YXZ' and 'ZXY' are all the same combination , but they are different permutations . It looks like you're interested in permutations, not combinations.

                Why do you want the permutations above, but not others, such as 'XZ' or 'ZYX'?

                To get all the permutations:
                SELECT  REPLACE ( SYS_CONNECT_BY_PATH (col, ',')
                          , ','
                          )     AS permutation
                FROM    t
                CONNECT BY NOCYCLE     col     != PRIOR col;
                Output:
                X
                XY
                XYZ
                XZ
                XZY
                Y
                YX
                YXZ
                YZ
                YZX
                Z
                ZX
                ZXY
                ZY
                ZYX
                To get all combinations, just use &gt; instead of != in the CONNECT BY clause:
                SELECT  REPLACE ( SYS_CONNECT_BY_PATH (col, ',')
                          , ','
                          )     AS combination
                FROM    t
                CONNECT BY NOCYCLE     col     > PRIOR col;
                Output:
                X
                XY
                XYZ
                XZ
                Y
                YZ
                Z
                One problem with joining is that you need to hard-code a separate copy of the table for each possible value. If you have 20 distinct values of col, then you need to join 20 copies of the table. CONNECT BY works with any number of values (0 or more).

                Edited by: Frank Kulash on Jan 2, 2013 7:15 AM
                Added query for combinations
                • 5. Re: SQL Query
                  Bilal
                  Dear All,

                  I was exactly looking for the SQL query written by Frank Kulash which is more generic and more scalable.

                  Thanks again for such nice responses.
                  Best Regards
                  Bilal
                  • 6. Re: SQL Query
                    ranit B
                    That's nice, but first learn how to ask questions properly.

                    Volunteers here work upon the inputs provided by the poster and please don't make feel as if we are wasting our time, working on wrong inputs.

                    Thanks.