882134 wrote:That's unusual. It's so unusual, one can't forbear wondering if you have the best possible table design.
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.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).
882134 wrote:Okay, but where do I find the CREATE TABLE and INSERT statements?
please find below erquisites
Table1Is 15 supposed to be in colum1, or colum3? How could anyone tell?
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
(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:
ENAME DEPTNO JOB COMM
` R_NUM DEPTNO JOB COMM
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:
WITH string_data AS
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:
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.