7 Replies Latest reply: Jan 18, 2013 12:38 PM by 885137 RSS

    Distinct Value from multiple fields

    885137
      Hi All,
      Good Day.

      I have one table which having 300 columns out of 300 columns I have to fetch DISTINCT value for 130 columns. If I do distinct one by one i.e write 130 sql query, will take lot of time. Is there any way I can do all 130 Columns DISTINCT value in a single statement/plsql proc.

      Thanks in advance

      deb
        • 1. Re: Distinct Value from multiple fields
          sb92075
          somebody would benefit from a course on Data Normalization.
          300 columns? REALLY?
          • 2. Re: Distinct Value from multiple fields
            Frank Kulash
            Hi, Deb,

            Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
            Simplify the problem. For example, if you saw a solution for a table with 5 columns, where distinct values mattered in 3 of the columns, would you then know how to solve your problem for 300 columns, in 130 of which uniquness matters? If so, post CREATE TABLE and INSERT staements for the simplified table. (Just mention how many columns you really have, so people give scalable solutions.)
            Explain, using specific examples, how you get those results from the sample data you post.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            882134 wrote:
            Hi All,
            Good Day.

            I have one table which having 300 columns out of 300 columns I have to fetch DISTINCT value for 130 columns.
            That's unusual. It's so unusual, one can't forbear wondering if you have the best possible table design.
            Is this actually a view, or some de-nomalized table as in a data warehouse, when some of the 130 columns depend on others? If so, you might be able to simplify the problem a bit.
            If I do distinct one by one i.e write 130 sql query, will take lot of time. Is there any way I can do all 130 Columns DISTINCT value in a single statement/plsql proc.
            It's unclear what you're trying to do. Whatever it is, I'll bet you don't need 130 separate queries. However, you might need a query with 130 similar expressions (e.g., 130 similar analytic functions, or 130 similar CASE expressions).
            You can probably reduce that by unpivoting your data, so that each row becomes as many as 130 rows, doing what you need to do in fewer columns, and then (if necessary) pivoting the results back to the original number of rows.
            • 3. Re: Distinct Value from multiple fields
              885137
              please find below erquisites

              Table1
              column1 column2 column3 column4 column5 ........ column300
              1 1 2 3 a
              1 2 4 2 c
              3 6 5 1 a
              4 1 2 1 d
              10 1 3 5 x
              4 6 4 5 c
              1 2 15 2 a


              expected result like

              column1 column2 column3 column4 column5
              1 1 2 1 a
              3 2 3 2 c
              4 6 4 3 d
              10 5 5 x
              15

              thanks
              Deb
              • 4. Re: Distinct Value from multiple fields
                885137
                Re-posting ny expected results

                TEST1 table

                c1---c2---c3---c4---c5---.. c300
                1-----4-----2----1----1......     
                2-----5-----3----2----2.....     
                4-----1----4-----3----2.....     
                3-----5----1-----1----1.....     
                6-----5----4-----1----1....     
                1-----5----2----2-----1....     
                2-----5----7----3-----1....     
                                         
                                         
                                         
                                         
                Expected Results_     
                                    
                c1--c2--c3--c4--c5--… c300
                1----1----1----1----1.......     
                2----4----2----2----2......     
                3----5----3----3...........
                4----------4.............          
                6----------7..............

                Edited by: 882134 on Jan 18, 2013 9:40 AM
                • 5. Re: Distinct Value from multiple fields
                  sb92075
                  the BOLDING is VERY helpful.
                  • 6. Re: Distinct Value from multiple fields
                    Frank Kulash
                    Hi, Deb,
                    882134 wrote:
                    please find below erquisites
                    Okay, but where do I find the CREATE TABLE and INSERT statements?
                    If you can show what you want to do using some commonly available table, then you don't have to post any sample data. For example, scott.emp has 14 rows and 8 columns. You might use scott.emp, showing distinct values for deptno (3 distinct values), job (5 distinct values) and comm (5 distinct values, counting NULLs).
                    Table1
                    column1 column2 column3 column4 column5 ........ column300
                    1 1 2 3 a
                    1 2 4 2 c
                    3 6 5 1 a
                    4 1 2 1 d
                    10 1 3 5 x
                    4 6 4 5 c
                    1 2 15 2 a


                    expected result like

                    column1 column2 column3 column4 column5
                    1 1 2 1 a
                    3 2 3 2 c
                    4 6 4 3 d
                    10 5 5 x
                    15
                    Is 15 supposed to be in colum1, or colum3? How could anyone tell?
                    This is just one of the reasons why you need to read the forum FAQ {message:id=9360002}

                    You may have noticed that this site normally doesn't display multiple spaces in a row.
                    Whenever you post formatted text (such as query results) on this site, type these 6 characters:

                    \
                    (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
                    
                    Let's take the example I suggested from scott.emp.
                    First, let's review the data in scott.emp:
                    SELECT     ename
                    ,     deptno
                    ,     job
                    ,     comm
                    FROM     scott.emp
                    ;
                    Output:
                    ENAME DEPTNO JOB COMM
                    ---------- ------ ---------- ------
                    SMITH 20 CLERK
                    ALLEN 30 SALESMAN 300
                    WARD 30 SALESMAN 500
                    JONES 20 MANAGER
                    MARTIN 30 SALESMAN 1400
                    BLAKE 30 MANAGER
                    CLARK 10 MANAGER
                    SCOTT 20 ANALYST
                    KING 10 PRESIDENT
                    TURNER 30 SALESMAN 0
                    ADAMS 20 CLERK
                    JAMES 30 CLERK
                    FORD 20 ANALYST
                    MILLER 10 CLERK
                    Ename actually has nothing to do with this problem; I included it just to show that you can ignore columns.  (Actually, we're ignoring the empno, mgr, sal and hiredate columns, too.)
                    Now say we want to see what the distinct values are for deptno, job and comm; that is, we want this output:
                    ` R_NUM DEPTNO JOB COMM
                    ---------- ---------- ---------- ----------
                    1 10 ANALYST 0
                    2 20 CLERK 1400
                    3 30 MANAGER 300
                    4 PRESIDENT 500
                    5 SALESMAN [NULL]
                    This is what I call +Pris-Fixe Output+ .  The output above does not imply that deptno=10 includes anyone whose job is 'ANALYST', or that anyone with that deptno or job has comm=0; it just so happens that the first values for deptno, job and comm are 10, 'ANALYST' and 0, respectively.
                    
                    Here's one way to get thiose results, but unpivoting and pivoting the data, as I suggested earlier:
                    WITH     string_data     AS
                    (
                         SELECT     TO_CHAR (deptno)      AS deptno_txt
                         ,     job
                         ,     TO_CHAR (comm)          AS comm_txt
                         FROM     scott.emp
                    )
                    ,     unpivoted_data     AS
                    (
                         SELECT     label
                         ,     NVL ( raw_txt
                              , '[NULL]'
                              )          AS txt
                         ,     DENSE_RANK () OVER ( PARTITION BY label
                                        ORDER BY      raw_txt
                                        )      AS r_num
                         FROM     string_data
                         UNPIVOT INCLUDE NULLS
                         ( raw_txt
                              FOR label IN ( deptno_txt
                                   , job
                                   , comm_txt
                                   )
                              )
                    )
                    SELECT     *
                    FROM     unpivoted_data
                    PIVOT     ( MIN (txt)
                         FOR label IN ( 'DEPTNO_TXT'     AS deptno
                                   , 'JOB'          AS job
                                   , 'COMM_TXT'          AS comm
                                   )
                         )
                    ORDER BY r_num
                    ;
                    SELECT ... PIVOT and UNPIVOT were new in Oracle 11.  This is one example of why it's important to say which version of Oracle you're using.
                    
                    If you have 130 columns to display (instead of 3), then you'll need 130 columns (instead of 3) in the SELECT clause of string_data, and 130 lines (instead of 3) in the UNPIVOT clause of the main query.  I know that's tedious, but it's not nearly as bad as 130 separate queries.
                    
                    Edited by: Frank Kulash on Jan 18, 2013 1:00 PM
                    Added pris-fixe query.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    • 7. Re: Distinct Value from multiple fields
                      885137
                      Hi Frank,

                      You are really great. Thanks for all you help. This code is working for me. going forward I also post query as you recomended.

                      Some body in this forum don't have any solution, they can only criticize people. you are totally different. Thanks Again.

                      Thanks
                      Deb

                      Edited by: 882134 on Jan 18, 2013 10:37 AM