This discussion is archived
7 Replies Latest reply: Jan 18, 2013 10:38 AM by 885137 RSS

Distinct Value from multiple fields

885137 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    somebody would benefit from a course on Data Normalization.
    300 columns? REALLY?
  • 2. Re: Distinct Value from multiple fields
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    the BOLDING is VERY helpful.
  • 6. Re: Distinct Value from multiple fields
    Frank Kulash Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points