Dynamic SQL formatting issue

Hi All,

I'm trying to write some dynamic SQL that reports a count of distinct values in each column for a given table.

I've written this:


------------------------------------------------

SPOOL COUNT_DISTINCTS.LST

SELECT 'SELECT '''||rpad(COLUMN_NAME,30,' ')||' '',COUNT(DISTINCT '||COLUMN_NAME||' ) FROM '||TABLE_NAME||';'
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '&TABLE_NAME'
;
SPOOL OFF

@COUNT_DISTINCTS.LST

------------------------------------------------

Which gives this:

Code:

---

ACT_RTL_NON_RTL_GRPG                                              2
ACT_HOLDER_TYPE                                              1
ACT_HOLDER_CATEGORY                                              2
ACT_CORP_NON_CORP_IND                                              1
ACT_ATM_TRANS_ACT_IND                                              2
ACT_DORM_ACCOUNT_PRD_IND                                              1

---

-----------------------------------------------

How do I get the numbers to line up!?

Many thanks in advance.

I changed the sql to use dba_tab_columns and picked a table that I had so that it would run.

Code:

---

SELECT 'SELECT '''||rpad(COLUMN_NAME,30,' ')||' ''||LPAD(COUNT(DISTINCT '||COLUMN_NAME||' ), 5) FROM '||owner||'.'||TABLE_NAME||';'
  FROM DBA_TAB_COLUMNS
 WHERE owner='RMANCAT'
  AND table_name='RSR'
 ORDER BY column_id;

---

You separated the two columns by a comma, plus having trimspool on probably caused the spaces to go away. If you connect to two columns with a double pipe (||) and use ltrim on the count then the output will line up.

Code:

---

RSR_KEY                          136
DBINC_KEY                          1
RSR_RECID                        136
RSR_STAMP                        120
RSR_PKEY                          49
RSR_L0KEY                        22
RSR_LEVEL                          3
RSR_TYPE                          3
RSR_OPER                          5
RSR_CMDID                        52
RSR_STATUS                        4
RSR_MBYTES                        29
RSR_START                        120
RSR_END                          94
RSR_IBYTES                        42
RSR_OBYTES                        49
RSR_OPTIMIZED                      1
RSR_OTYPE                          4
RSR_SRECID                        51
RSR_SSTAMP                        51
RSR_ODEVTYPE                      1
SITE_KEY                          1
RSR_OSB_ALLOCATED                  1

---

Perhaps this:

Code:

---

SQL> SELECT x.column_name,
  2        TO_NUMBER(EXTRACTVALUE(
  3            DBMS_XMLGEN.getxmltype(
  4      'select count(DISTINCT '||x.column_name||') counter from '|| x.owner||'.'||x.table_name ),
  5      '/ROWSET/ROW/COUNTER' ) )
  6          num_distinct
  7    FROM dba_tab_columns x
  8  WHERE owner = 'SCOTT'
  9    AND table_name = 'EMP'
 10  ORDER BY x.column_id;

COLUMN_NAME          NUM_DISTINCT
-------------------- ------------
EMPNO                          14
ENAME                          14
JOB                            5
MGR                            6
HIREDATE                      13
SAL                            12
COMM                            4
DEPTNO                          3

8 rows selected.

---

:p

Thanks Guys,

I've gone with Gandolf's solution as it's a simple tweak to my existing code. Works a treat!

Horace.