After filling in the blanks and executing the query, I receive an unexpected result set from the CASE statement:
It returns values for wk1, wk2 then skips to wk20 and wk24. I was expecting a cumulative count such that even if there was no activity was completed within the week of (sysdate-21), then wk3 = wk2. However, when I select distinct values with the CASE statement there are only the PERIODs (wk01, wk02, wk20, wk24).

Any idea why that should happen? Looking at the query it shouldn't behave that way

Tom,
When you look at the initially posted SQL query, you will notice that the count is cummulative over the 24 week period such that by week 24, we are seeing all the records grouped by CR_type completed within the past 24 weeks. That is the result set I wanted to pivot

The approach you suggested groups the records by the week they were completed, such that it is not cumulative over time. I hope that the problem statement is clear enough.

Thanks for all the good work Tom.

Tom,
How could I get this output:

mgr mgr_employees
13 jones,blake,clark

from this data?

>select ename from emp where mgr=7839;

ENAME
------------------------------
JONES
BLAKE
CLARK

3 rows selected.

Thanks.

Oracle 8.1.7.4 on HP Unix

Tom,

I am trying to pivot a rather large query..and hitting the max length of long variable which is 32760.
I have declared a variable as long...and in that varable..I put in the concatenated string to build
--max( decode, col, "something", null):

WHILE C_OUT_REC_MAX%FOUND LOOP

DBMS_OUTPUT.PUT_LINE ( LENGTH (vMAX) );

IF vMAX IS NULL THEN
vMAX := 'MAX( DECODE (' || pCOLID || ', ' || vMAXID || ', PROJECT_PER_EMP, NULL) ) "' || vMAXID || '"';
ELSE
vMAX := vMAX || ',' || CHR(10) ||
'MAX( DECODE (' || pCOLID || ', ' || vMAXID || ', PROJECT_PER_EMP, NULL) ) "' || vMAXID || '"';

END IF;

FETCH C_OUT_REC_MAX INTO vMAXID, vMAXDS;

END LOOP;


........


This query has lot of columns to pivot....
so I am hitting the limit on long variable.

Is there a better way to do this?

Thanks.

After getting vMAX...i was going to concatenate vMAX to a 'SELECT '...
so they will be individual columns.
I am doing this in PL/SQL because I don't know the no. of the pivoting column ahead of time...

Here is the query I am pivoting:

SELECT EMPLID,
MAX( DECODE (GSAID, 11, PROJECT_PER_EMP_GSA, NULL) ) "11",
MAX( DECODE (GSAID, 11.5, PROJECT_PER_EMP_GSA, NULL) ) "11.5"
FROM

(
SELECT A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
FROM
(SELECT E.EMPLID,
P.GSAID,
COUNT(P.PROJID) OVER(PARTITION BY E.EMPLID, P.GSAID) PROJECT_PER_EMP_GSA
FROM EM_PROJ E, PROJ_GSA P
WHERE E.PROJID = P.PROJID
AND EMPLID IN ('X1', 'X2')
AND GSAID IN (11,11.5)
) A
GROUP BY A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
)

GROUP BY EMPLID;

EMPLID 11 11.5
----------- ---------- ----------
X1 1 2
X2 5 9
2 rows selected.


Now this is when I know the GSAID'S I need to pivot by...
If I take the "AND GSAID IN (11,11.5)" clause out...
I have no control over GSAID's...
I can get to them with a query...but i don't know how to build
MAX( DECODE (GSAID, 11, PROJECT_PER_EMP_GSA, NULL) ) "11",
MAX( DECODE (GSAID, 11.5, PROJECT_PER_EMP_GSA, NULL) ) "11.5"
.....
....

clause just in sql.

This same query will decide the possible GSAID's to pivot on
so I have to actually run this query twice..
once to get the distinct GSAID'S to pivot on...
and then to get the pivoted resultset.

So, I have to turn to PL/SQL..and build the MAX clause in there..
and that's where i hit the long limit.

Tom...
Thanks for your earlier help...
So now I have the query but when I try to run it in sql plus..
I get ORA-01467: sort key too long...
Documentation suggests:
Reduce the number of columns or group functions involved in the operation.

I am grouping by just two columns..rest are max(decode...)
columns that i get after pivoting.

So, I was considering may be reducing the no. of columns in one record set..like breaking up the recordset...may be in to two or three...

What is the limit on number of columns that go in one recordset....like I can have maximum 770 columns...so can I break it up into may be 3 recordsets....250 each...

or is there a better way to deal with this error?

Thanks

SELECT EMPLID,
MAX( DECODE (GSAID, 11, PROJECT_PER_EMP_GSA, NULL) ) THE_11,
MAX( DECODE (GSAID, 11.5, PROJECT_PER_EMP_GSA, NULL) ) THE_11_5
FROM

(
SELECT A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
FROM
(SELECT E.EMPLID,
P.GSAID,
COUNT(P.PROJID) OVER(PARTITION BY E.EMPLID, P.GSAID) PROJECT_PER_EMP_GSA
FROM EM_PROJ E, PROJ_GSA P
WHERE E.PROJID = P.PROJID
AND EMPLID IN ('X1', 'X2')
AND GSAID IN (11,11.5)
) A
GROUP BY A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
)

GROUP BY EMPLID;


"
what if you pivot the other way instead? put the group by ACROSS and the
current "across" columns "down"
"

-->

like pivot on emplid?
Wouldn't the resultset be totally different then?

When confronted with a similar problem (having to pivot an unknown number of columns), I implemented the following, which works for me. It is run from a SQL script on the unix host, and spools a new sql file, which the original script executes later.

/**************************************************
** Start a query that writes SQL for a crosstab **
** Takes all segment_type values in a table and **
** turns them into row headings **
**************************************************/
spool rf_out.sql
PROMPT select region
select ', sum(decode(segment_type,''' ||segment_type||''',1,0))' || segment_type
from ull_comp group by segment_type;
select ', count(1) TOTAL
from ull_comp
group by region;' from dual
/

spool off

@rf_out

/*******************************************/

The example above gives a count of segments for each region. The PROMPT, and first line of Select statement, are creating the new select statement.

The result is a query that lists each segment type, in the body of the decode statement, that is checked later on.

I am not sure if there are the same limitations of size (32k) in a SQL statement like this

Tom,

pivoting the other way..is this what you were suggesting:

SELECT GSAID,
MAX( DECODE (EMPLID, 'x1', PROJECT_PER_EMP_GSA, NULL) ) "x1",
MAX( DECODE (EMPLID, 'x2', PROJECT_PER_EMP_GSA, NULL) ) "x2"
FROM

(
SELECT A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
FROM
(SELECT E.EMPLID,
P.GSAID,
COUNT(P.PROJID) OVER(PARTITION BY E.EMPLID, P.GSAID) PROJECT_PER_EMP_GSA
FROM EM_PROJ E, PROJ_GSA P
WHERE E.PROJID = P.PROJID
AND EMPLID IN ('x1', 'x2')
AND GSAID IN (11,11.5)
) A
GROUP BY A.EMPLID, A.GSAID, A.PROJECT_PER_EMP_GSA
)

GROUP BY GSAID;

GSAID x1 x2
---------- ---------- ----------
11 1 5
11.5 2 9
2 rows selected.

Well there are more employees than GSA codes...so turning it around won't do me any good...either...

I was able to help the "sort key too long" problem in my application by by reducing the column width in my query.

The table column was defined as varchar2(400) but the specific data I was using in my query was always much less than that..

I changed this:
MAX( DECODE (GSAID, 11, PROJECT_PER_EMP_GSA, NULL) ) THE_11,

to this:
MAX( DECODE (GSAID, 11, substr(PROJECT_PER_EMP_GSA,1,40), NULL) ) THE_11,

and it gave me enough extra room to do what I needed.

I need to get pivot results for those employees whose deptno belongs to deptno of enames matching 'CLA%'.
The inner query returs 3 rows but the pivot gives me columns for ename_6 also.???( pls note there are 2 names like CLA in empl table )
If create a temp table for the inner query it gives me the right results. How to solve this dup problem.

Thanx
1)
SELECT d.deptno,
e.ename,
e.sal,
e.job,
ROW_NUMBER () OVER (PARTITION BY e.deptno ORDER BY job) rn
FROM dept d, empl e
WHERE e.deptno = d.deptno
AND d.deptno IN (SELECT deptno
FROM empl
WHERE ename LIKE 'CLA%')

DEPTNO ENAME SAL JOB RN
------ ---------- --------- --------- ---------------------------------------
10 CLARKEY 1300.00 CLERK 1
10 CLARK 2450.00 MANAGER 2
10 KING 5000.00 PRESIDENT 3
3 rows selected

2)
SELECT deptno,
MAX (DECODE (rn, 1, ename, NULL)) ename_1,
MAX (DECODE (rn, 1, sal, NULL)) sal_1,
MAX (DECODE (rn, 1, job, NULL)) job_1,
MAX (DECODE (rn, 2, ename, NULL)) ename_2,
MAX (DECODE (rn, 2, sal, NULL)) sal_2,
MAX (DECODE (rn, 2, job, NULL)) job_2,
MAX (DECODE (rn, 3, ename, NULL)) ename_3,
MAX (DECODE (rn, 3, sal, NULL)) sal_3,
MAX (DECODE (rn, 3, job, NULL)) job_3,
MAX (DECODE (rn, 4, ename, NULL)) ename_4,
MAX (DECODE (rn, 4, sal, NULL)) sal_4,
MAX (DECODE (rn, 4, job, NULL)) job_4,
MAX (DECODE (Rn, 5, ename, NULL)) ename_5,
MAX (DECODE (rn, 5, sal, NULL)) sal_5,
MAX (DECODE (rn, 5, job, NULL)) job_5,
MAX (DECODE (rn, 6, ename, NULL)) ename_6,
MAX (DECODE (rn, 6, sal, NULL)) sal_6,
MAX (DECODE (rn, 6, job, NULL)) job_6
FROM (SELECT d.deptno,
e.ename,
e.sal,
e.job,
ROW_NUMBER () OVER (PARTITION BY D.deptno ORDER BY job) rn
FROM dept d, empl e
WHERE e.deptno = d.deptno
AND d.deptno IN (SELECT deptno
FROM empl
WHERE ename LIKE 'CLA%'))
GROUP BY DEPTNO;
o/p:
DEPTNO ENAME_1 SAL_1 JOB_1 ENAME_2 SAL_2 JOB_2 ENAME_3 SAL_3 JOB_3 ENAME_4 SAL_4 JOB_4 ENAME_5 SAL_5 JOB_5 ENAME_6 SAL_6 JOB_6
10 CLARKEY 1300 CLERK CLARKEY 1300 CLERK CLARK 2450 MANAGER CLARK 2450 MANAGER KING 5000 PRESIDENT KING 5000 PRESIDENT

3)
CREATE TABLE TEMP_EMPL AS
SELECT d.deptno,
e.ename,
e.sal,
e.job,
ROW_NUMBER () OVER (PARTITION BY e.deptno ORDER BY job) rn
FROM dept d, empl e
WHERE e.deptno = d.deptno
AND d.deptno IN (SELECT deptno
FROM empl
WHERE ename LIKE 'CLA%')
SELECT deptno,
MAX (DECODE (rn, 1, ename, NULL)) ename_1,
MAX (DECODE (rn, 1, sal, NULL)) sal_1,
MAX (DECODE (rn, 1, job, NULL)) job_1,
MAX (DECODE (rn, 2, ename, NULL)) ename_2,
MAX (DECODE (rn, 2, sal, NULL)) sal_2,
MAX (DECODE (rn, 2, job, NULL)) job_2,
MAX (DECODE (rn, 3, ename, NULL)) ename_3,
MAX (DECODE (rn, 3, sal, NULL)) sal_3,
MAX (DECODE (rn, 3, job, NULL)) job_3,
MAX (DECODE (rn, 4, ename, NULL)) ename_4,
MAX (DECODE (rn, 4, sal, NULL)) sal_4,
MAX (DECODE (rn, 4, job, NULL)) job_4,
MAX (DECODE (Rn, 5, ename, NULL)) ename_5,
MAX (DECODE (rn, 5, sal, NULL)) sal_5,
MAX (DECODE (rn, 5, job, NULL)) job_5,
MAX (DECODE (rn, 6, ename, NULL)) ename_6,
MAX (DECODE (rn, 6, sal, NULL)) sal_6,
MAX (DECODE (rn, 6, job, NULL)) job_6
FROM (SELECT * FROM TEMP_EMPL)
GROUP BY DEPTNO ;
o/p:
DEPTNO ENAME_1 SAL_1 JOB_1 ENAME_2 SAL_2 JOB_2 ENAME_3 SAL_3 JOB_3 ENAME_4 SAL_4 JOB_4 ENAME_5 SAL_5 JOB_5 ENAME_6 SAL_6 JOB_6
10 CLARKEY 1300 CLERK CLARK 2450 MANAGER KING 5000 PRESIDENT
********************************************************
CREATE TABLE DEPT (
DEPTNO NUMBER (2) NOT NULL,
DNAME VARCHAR2 (14),
LOC VARCHAR2 (13),
CONSTRAINT PK_DEPT
PRIMARY KEY ( DEPTNO ) );
CREATE TABLE EMPL (
EMPNO NUMBER (4),
ENAME VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR NUMBER (4),
HIREDATE DATE,
SAL NUMBER (7,2),
COMM NUMBER (7,2),
DEPTNO NUMBER (2));
ALTER TABLE EMPl ADD CONSTRAINT FK_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO);
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
COMMIT;
INSERT INTO EMPL ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
'7782', 'CLARK', 'MANAGER', '7839', TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '2450', NULL, 10);
INSERT INTO EMPL ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
'7839', 'KING', 'PRESIDENT', NULL, TO_Date( '11/17/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '5000', NULL, 10);
INSERT INTO EMPL ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
'7934', 'CLARKEY', 'CLERK', '7782', TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, '1300', NULL, 10);
COMMIT;

I am surprised why you dont get the same result.
I am listing the individual tables values and the query I put with rowid.
You see the rowids are repeated for rn = 4,5,6 whereas the
inner table just has 3 rows 1,2,3.
Any clues to this.
Thanx for your Help.



SQL> select * from dept ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from empl ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 CLARKEY    CLERK           7782 23-JAN-82       1300                    10

SELECT d.rowid ri_dept,e.rowid ri_emp,d.deptno,
               e.ename,
               e.sal,
               e.job,
               ROW_NUMBER () OVER (PARTITION BY D.deptno ORDER BY job) rn
          FROM dept d, empl e
         WHERE e.deptno = d.deptno
           AND d.deptno IN (SELECT deptno
                              FROM empl
                             WHERE ename LIKE 'CLA%')
AAALkgAABAAAM/SAAA    AAALzIAAJAAAC80AAC    10    CLARKEY    1300    CLERK    1
AAALkgAABAAAM/SAAA    AAALzIAAJAAAC80AAA    10    CLARK    2450    MANAGER    2
AAALkgAABAAAM/SAAA    AAALzIAAJAAAC80AAB    10    KING    5000    PRESIDENT    3

SELECT deptno,
       MAX (DECODE (rn, 1, ename, NULL)) ename_1,
       MAX (DECODE (rn, 1, sal, NULL)) sal_1,
       MAX (DECODE (rn, 1, job, NULL)) job_1,
       MAX (DECODE (rn, 1, ri_emp, NULL)) ri_emp_1,
       MAX (DECODE (rn, 2, ename, NULL)) ename_2,
       MAX (DECODE (rn, 2, sal, NULL)) sal_2,
       MAX (DECODE (rn, 2, job, NULL)) job_2,
              MAX (DECODE (rn, 2, ri_emp, NULL)) ri_emp_2,
       MAX (DECODE (rn, 3, ename, NULL)) ename_3,
       MAX (DECODE (rn, 3, sal, NULL)) sal_3,
       MAX (DECODE (rn, 3, job, NULL)) job_3,
              MAX (DECODE (rn, 3, ri_emp, NULL)) ri_emp_3,
       MAX (DECODE (rn, 4, ename, NULL)) ename_4,
       MAX (DECODE (rn, 4, sal, NULL)) sal_4,
       MAX (DECODE (rn, 4, job, NULL)) job_4,
              MAX (DECODE (rn, 4, ri_emp, NULL)) ri_emp_4,
       MAX (DECODE (Rn, 5, ename, NULL)) ename_5,
       MAX (DECODE (rn, 5, sal, NULL)) sal_5,
       MAX (DECODE (rn, 5, job, NULL)) job_5,
              MAX (DECODE (rn, 5, ri_emp, NULL)) ri_emp_5,
       MAX (DECODE (rn, 6, ename, NULL)) ename_6,
       MAX (DECODE (rn, 6, sal, NULL)) sal_6,
       MAX (DECODE (rn, 6, job, NULL)) job_6,
              MAX (DECODE (rn, 6, ri_emp, NULL)) ri_emp_6
  FROM (SELECT d.rowid ri_dept,e.rowid ri_emp,d.deptno,
               e.ename,
               e.sal,
               e.job,
               ROW_NUMBER () OVER (PARTITION BY D.deptno ORDER BY job) rn
          FROM dept d, empl e
         WHERE e.deptno = d.deptno
           AND d.deptno IN (SELECT deptno
                              FROM empl
                             WHERE ename LIKE 'CLA%'))
                             
                             GROUP BY DEPTNO    ;

DEPTNO    ENAME_1    SAL_1    JOB_1    RI_EMP_1    ENAME_2    SAL_2    JOB_2    RI_EMP_2    ENAME_3    SAL_3    JOB_3    RI_EMP_3    ENAME_4    SAL_4    JOB_4    RI_EMP_4    ENAME_5    SAL_5    JOB_5    RI_EMP_5    ENAME_6    SAL_6    JOB_6    RI_EMP_6
10    CLARKEY    1300    CLERK    AAALzIAAJAAAC80AAC    CLARKEY    1300    CLERK    AAALzIAAJAAAC80AAC    CLARK    2450    MANAGER    AAALzIAAJAAAC80AAA    CLARK    2450    MANAGER    AAALzIAAJAAAC80AAA    KING    5000    PRESIDENT    AAALzIAAJAAAC80AAB    KING    5000    PRESIDENT    AAALzIAAJAAAC80AAB 

Thanx a lot for your help. the rownum trick worked.
1)Can you kindly explain why/how rownum > 0 that fixed the problem . How could one come up with this witty solution ? You are great

BTW  the version I tested this is
SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production.

The same thing happens even for Oracle 9.2 on Linux
select * from v$version

BANNER
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0    Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
5 rows selected 

Hi tom,

I have a table with two columns and the values are:
advert_id advert_text
--------------------------------
1 AAA
1 BBB
2 CCC
2 DDD
3 EEE


Can I write a sql query to rollup the above resultset based on unique
advert_id column to get following
result

advert_id advert_text
----------------------------------
1 AAABBB
2 CCCDDD
3 EEE

Thanks,

But, can we do it in single SQL statement and avoid PL/SQL.

Thanks again

Hi Tom,

Great information. Makes me think pivoting might be a solution to my problem. I have a query tht uses UNION ALL to produce the output i want (there will not be any duplicates). But the bulk of data in all tables is very high and the select takes an hour to produce an output. Two FTS happen and the indexes just do not get used (tried computing and deleting statistics). I need to do this in a sigle query because have to use the results for comparison in another big table. Here is a sample scenario:

CREATE TABLE caa0 (
pk NUMBER(5), p_rel VARCHAR2(20),
PRIMARY KEY (PK))

INSERT INTO CAA0 ( PK, P_REL) VALUES ('1', 'P_ACC1');
INSERT INTO CAA0 ( PK, P_REL ) VALUES ('2', 'P_ACC2');
INSERT INTO CAA0 ( PK, P_REL) VALUES ( '3', 'P_ACC3');

CREATE TABLE caa2(
pk NUMBER(5), S_rel VARCHAR2(20))

CREATE INDEX SEC_IDX ON CAA2 (pk)

INSERT INTO CAA2 ( PK, S_REL ) VALUES ('1', 'S_ACC11');
INSERT INTO CAA2 ( PK, S_REL) VALUES ( '1', 'S_ACC12');
INSERT INTO CAA2 ( PK, S_REL) VALUES ( '3', 'S_ACC31');

SELECT caa0.pk, caa0.p_rel, caa0.p_rel rel
FROM caa0
UNION ALL
SELECT
caa0.pk, caa0.p_rel, caa2.S_REL rel
FROM caa0, caa2
WHERE caa0.pk = caa2.pk

PK P_REL REL
1 P_ACC1 P_ACC1
2 P_ACC2 P_ACC2
3 P_ACC3 P_ACC3
1 P_ACC1 S_ACC11
1 P_ACC1 S_ACC12
3 P_ACC3 S_ACC31

I need values from both caa0 and caa2 in a single column. caa2 might not have a record related to caa0.

Is this a case for pivoting? Is there another better way to do it in a single SQL so that i getter better performance?

Thanks a lot for your help.

Outer join will give me p_rel and s_rel in two different columns. The result of the outer join will be:
PK P_REL S_REL
---------------------------------------
1 P_ACC1 S_ACC11
1 P_ACC1 S_ACC12
2 P_ACC2
3 P_ACC3 S_ACC31

What we want here is all the values of p_rel from caa0 and all related values of s_rel in one single column. Like this:

PK REL
---------------------------
1 P_ACC1
1 S_ACC11
1 S_ACC12
2 P_ACC2
3 P_ACC3
3 S_ACC31

Am really struggling with this problem. The caa0 table has arounf 12 million records which amounts to about 12gb of data. caa2 is lesser though... around 1.5 million. But whenever i run the query with UNION ALL, it goes for a toss...:-(

I have searched and read, know I am close, but I've been stuck for some time and have to ask..

I have a table as follows:

ID Message
----------------
1242 I have
1243 succeeded
1245 my results.
1244 in pivoting

I would like to query this (order by ID) and pivot the result to create a single message:

"I have succeeded in pivoting my results."

The number of rows WILL vary. I have used STRAGG which is great but it will not allow me to order the result by ID making the message intelligible. How can this be accomplished? I am on 9i.

Hi Tom,

When I query user_tab_columns table:
select table_name,column_name from user_tab_columns;
I get the following results:

TABLE_NAME COLUMN_NAME
------------------------------ -------------
BONUS ENAME
BONUS JOB
BONUS SAL
BONUS COMM
DEPT DEPTNO
DEPT DNAME
DEPT LOC
EMP EMPNO
EMP ENAME
EMP JOB
EMP MGR
EMP HIREDATE
EMP SAL
EMP COMM
EMP DEPTNO

How can I use a query to remove the repeated table names and get a result like the following:

TABLE_NAME COLUMN_NAME
------------------------------ -------------
BONUS ENAME
JOB
SAL
COMM
DEPT DEPTNO
DNAME
LOC
EMP EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

Thank you very much for your help in advance.

SQL> break on table_name
SQL> select table_name, column_name from user_tab_columns ORDER BY TABLE_NAME; 

Tom,

I hope this is on the subject.

What is a simple query that will read DBA_CONS_COLUMNS and return 2 columns: col1=table_name, col2=comma delimited list of column_names (e.g. primary key values)

Table Column Names
------------------ --------------------------
CARS MAKE,MODEL,YEAR
ORDER_LINES ORDER_NUMBER,LINE_NUMBER
PAY_US_ZIP_CODES ZIP_START,ZIP_END,STATE_CODE,COUNTY_CODE,CITY_CODE

Thanks,

Robert

Tom,

This is for an 8.1.7.4 database.

Thanks,

Robert

Tom,

Is this the best answer?

select owner
, constraint_name
, rtrim(max(decode(position, 1, column_name, null)) || ',' ||
max(decode(position, 2, column_name, null)) || ',' ||
max(decode(position, 3, column_name, null)) || ',' ||
max(decode(position, 4, column_name, null)) || ',' ||
max(decode(position, 5, column_name, null)) || ',' ||
max(decode(position, 6, column_name, null)) || ',' ||
max(decode(position, 7, column_name, null)) || ',' ||
max(decode(position, 8, column_name, null)) || ',' ||
max(decode(position, 9, column_name, null)),',') column_names
from dba_cons_columns
where owner = 'HR'
and constraint_name = 'PAY_US_ZIP_CODES_PK'
group by owner
, constraint_name
/

Thanks,

Robert.

i have one issue, my table is something like this.


id code name pid

1 JNR Jayanagar 0
2 BTM BTM Layout 0
3 BSK BanaSankari 0
4 W1 N1 1
5 W2 N2 1
6 W3 N3 1
7 W4 N4 2
8 W5 N5 2
9 W6 N6 3
10 W7 N7 3



I Want Query to return 3 rows(dynamically)
JNR N1,N2,N3
BTM N4,N5
BSK N6,N7


Thanks,
Pushpendra Singh

thanks a lot, this solution is ok but not acceptable for me. Because I dont want (cant) specify where clause.

for example, in this query


select owner
, constraint_name
, rtrim(max(decode(position, 1, column_name, null)) || ',' ||
max(decode(position, 2, column_name, null)) || ',' ||
max(decode(position, 3, column_name, null)) || ',' ||
max(decode(position, 4, column_name, null)) || ',' ||
max(decode(position, 5, column_name, null)) || ',' ||
max(decode(position, 6, column_name, null)) || ',' ||
max(decode(position, 7, column_name, null)) || ',' ||
max(decode(position, 8, column_name, null)) || ',' ||
max(decode(position, 9, column_name, null)),',') column_names
from dba_cons_columns
where owner = 'HR'
and constraint_name = 'PAY_US_ZIP_CODES_PK'
group by owner
, constraint_name



can we get the same result if we dont have column 'position' in the table or without using this column in query anywhere.

it is something hardcodeing position numbers, but in my case i cant have this type of field.

Thanks
Pushpendra

Tom ,

I have

create table mouse_Table ( id number(22), room number(5) , project_id number(22) , genotype varchar2(3) , sex varchar2(3))

Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(73, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(74, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(75, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(76, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(77, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(78, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(79, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(80, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(82, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(0, '247A', 2000, 'unav', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(0, '247A', 2000, 'unav', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(0, '247A', 2000, 'unav', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(1, '247A', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(2, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(3, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(4, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(5, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(6, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(31, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(40, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(41, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(42, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(43, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(44, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(64, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(65, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(66, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(67, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(68, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(69, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(71, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(92, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(93, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(94, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(95, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(96, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(97, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(98, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(99, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(101, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(136, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(137, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(138, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(139, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(140, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(141, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(173, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(175, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(176, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(177, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(156, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(157, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(158, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(159, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(160, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(200, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(201, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(202, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(203, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(204, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(205, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(206, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(207, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(208, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(209, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(210, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(211, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(192, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(193, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(194, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(197, '208', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(199, '208', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(46, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(47, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(48, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(51, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(54, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(55, '247', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(56, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(57, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(58, '247', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(59, '247', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(60, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(61, '247', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(62, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(32, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(33, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(34, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(35, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(36, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(37, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(38, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(39, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(24, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(25, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(26, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(27, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(28, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(7, '247A', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(8, '247A', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(9, '247A', 2000, 'TBD', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(10, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(11, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(12, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(13, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(14, '247A', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(17, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(18, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(19, '247A', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(20, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(21, '247A', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(83, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(84, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(85, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(86, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(87, '247', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(90, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(91, '247', 2000, 'wt', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(127, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(130, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(132, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(134, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(181, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(182, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(183, '208', 2000, 'TBD', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(184, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(105, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(110, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(125, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(126, '208', 2000, 'TBD', 'TBD');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(162, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(163, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(164, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(165, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(166, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(167, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(168, '208', 2000, 'wt', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(171, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(172, '208', 2000, 'hom', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(143, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(144, '208', 2000, 'hom', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(145, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(146, '208', 2000, 'hom', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(147, '208', 2000, 'hom', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(149, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(150, '208', 2000, 'hom', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(151, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(152, '208', 2000, 'het', 'M');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(154, '208', 2000, 'het', 'F');
Insert into MOUSE_TABLE
(ID, ROOM, PROJ_ID, GENOTYPE, SEX)
Values
(155, '208', 2000, 'het', 'F');
COMMIT;


I want a pivot query which would give
room , 'het'_M_count , 'hom'_M_count , 'wt'_M_count and similar for female

arg, don't you just hate it when people use numbers to store strings (fixed that for you - room is a string).  

arg, point 2, don't you just hate it when people use one column name in the create table and another in the insert?

arg, point 3, we could have gotten away with a slightly more concise example :) bigger is not always better...

this is a pretty simple pivot, did you search for pivot on this site?  and see the examples. 


ops$tkyte@ORA9IR2> select room,
  2  count( case when sex = 'M' and genotype = 'het' then 1 end ) het_m_count,
  3  count( case when sex = 'F' and genotype = 'het' then 1 end ) het_f_count,
  4  count( case when sex = 'M' and genotype = 'hom' then 1 end ) hom_m_count,
  5  count( case when sex = 'F' and genotype = 'hom' then 1 end ) hom_f_count,
  6  count( case when sex = 'M' and genotype = 'wt'  then 1 end ) wt_m_count,
  7  count( case when sex = 'F' and genotype = 'wt' then 1 end ) wt_f_count
  8  from mouse_table
  9  group by room
 10  /

ROOM  HET_M_COUNT HET_F_COUNT HOM_M_COUNT HOM_F_COUNT WT_M_COUNT WT_F_COUNT
----- ----------- ----------- ----------- ----------- ---------- ----------
208            19          12           8           4         12          2
247             0           1           0           0          3          9
247A            2           0           0           0         16         17

 

I forgot to add -

this is what i did

select room
sum(max(decode(mt.genotype,'wt',1,0)) + max(decode(mt.sex,'M',1,0))) as room_M_wt_count,
sum(max(decode(mt.genotype,'het',1,0)) + max(decode(mt.sex,'M',1,0))) as room_M_het_count,
sum(max(decode(mt.genotype,'hom',1,0)) + max(decode(mt.sex,'M',1,0))) as room_M_hom_count,
sum(max(decode(mt.genotype,'TBD',1,0)) + max(decode(mt.sex,'M',1,0))) as room_M_TBD_count
from mouse_Table mt where proj_id = 2000 group by genotype , sex , room

Hi Tom,

I'm currently tuning a query that requires the resultset to be pivoted. I am pivoting via the following template:
select <primary key value>,max<column 1>, max<column 2>,...<column n>
from
(select <primary key value>,
decode(field_name,<filter to identify column 1>,<field value>) as <column name 1>,
decode(field_name,<filter to identify column 2>,<field value>) as <column name 2>
from <materialized view>
...)
group by <primary key value;

The trace file reveals the CBO optimizer performing a full scan of the materialized view while running through the "SQL Execution" phase. The timing from start to finish was around 30 minutes. Stats gathered are current.

However, for kicks, I placed the subquery in a temp table, replaced the from subquery with the name of the temp table, and re-ran the query ie

1) create global temporary table temp_arun on commit preserve rows as <subquery in previous from clause>;

2) select <primary key value>,max<column 1>, max<column 2>,...<column n>
from
(temp_arun)
group by <primary key value;

This time, it ran in slightly over a minute, as the optimizer simply had to fully scan the rows of the temporary table.

I'd like to rewrite my original query such that the optimizer will process it similar to how it did when I experimented with the temporary table. Any suggestions on where I can troubleshoot are greatly appreciated.

I apologize in advance for not pasting any trace and SQL*Plus sessions, due to the sensitivity of the data.

Hope all is well, and thanks again for being a great virtual mentor over the years.

Regards,
Arun