Home » SQL & PL/SQL » SQL & PL/SQL » Column Formatting (merged)
Column Formatting (merged) [message #347277] Thu, 11 September 2008 04:19 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,
I have created the following package to display the value of the column period_name as the column headings and it seems to be working fine except it has lot of spaces while displaying the values. There is a huge gap between the columns, I want to reduce this gap.


Prime     AP01-08    AP02-08    AP03-08    AP04-08   AP05-08    AP06-08    Count
--------- ---------- ---------- ---------- --------- ---------- ---------- ---------
0080      0          0          1          2         0          0          3
0081      1          0          0          1         0          0          2


I would like to see the output like below,
Prime AP01-08  AP02-08  AP03-08 AP04-08  AP05-08 AP06-08 Count
---------------------------------------------------------------
0080  0        0        1       2        0       0       3
0081  1        0        0       1        0       0       2

Column col_name format A6 - cannot be used in this scenario since the column names AP01-08,AP02-08 are dynamically created and they may vary for each.

CREATE TABLE EXEL_GLR_CCLINES_TMP ( 
  SEGMENT5     VARCHAR2 (5), 
  PERIOD_NAME  VARCHAR2 (7), 
  CNT          NUMBER (3))

INSERT INTO exel_glr_cclines_tmp VALUES ('0080','AP01-08','0')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0080','AP02-08','0')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0080','AP03-08','1')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0080','AP04-08','2')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0081','AP01-08','1')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0081','AP02-08','0')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0081','AP03-08','0')
/

INSERT INTO exel_glr_cclines_tmp VALUES ('0081','AP04-08','1')
/

CREATE OR REPLACE PACKAGE exlpivot
AS
   TYPE rc IS REF CURSOR;
   PROCEDURE DATA ( p_cursor IN OUT rc );
END;
/

CREATE OR REPLACE PACKAGE BODY exlpivot
AS
PROCEDURE data( p_cursor IN OUT rc )
IS
    l_stmt LONG;
BEGIN
    l_stmt := 'select distinct substr(segment5,1,5)"Prime"';
    FOR x IN ( SELECT DISTINCT SUBSTR(period_name,1,7)period_name FROM exel_glr_cclines_tmp ORDER BY 1 )
    LOOP
        l_stmt := l_stmt || ', LTRIM(max(decode(substr(period_name,1,7),' || '''' 
                || SUBSTR(x.period_name,1,7) || '''' || ',cnt,0))) "'  || SUBSTR(x.period_name,1,7) || '"' ;
    END LOOP;
    l_stmt := l_stmt || ',Sum(cnt) "Count" ';
    l_stmt := l_stmt || ' from exel_glr_cclines_tmp group by segment5 order by 1';
    OPEN p_cursor FOR l_stmt;
END data;
END;
/



Can someone help me please.

[Mod-Edit: Frank reformatted long lines for better visibility]

[Updated on: Thu, 11 September 2008 05:08] by Moderator

Report message to a moderator

Re: Column Formatting [message #347289 is a reply to message #347277] Thu, 11 September 2008 04:35 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
What statement did you tried to get the output?

[Updated on: Thu, 11 September 2008 04:37]

Report message to a moderator

Re: Column Formatting [message #347294 is a reply to message #347277] Thu, 11 September 2008 04:38 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Sorry forget to mention that,

SET AUTOPRINT ON
Variable x refcursor;
Exec exlpivot.data(:x);
Re: Column Formatting [message #347295 is a reply to message #347277] Thu, 11 September 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports
Section Formatting Columns
Chapter 12 SQL*Plus Command Reference
Section COLUMN

Regards
Michel

[Updated on: Thu, 11 September 2008 04:41]

Report message to a moderator

Re: Column Formatting (merged) [message #347311 is a reply to message #347277] Thu, 11 September 2008 04:57 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

I reviewed the document links provided by you and tried using the set LONG and SET LONGC but it hasn't helped me.

The column names AP01-08,AP02-08.. is dynamically created so I wont be able to use COL AP01-08 format A5 since if I ran it for another period 2007, the column headings will be AP01-07,AP02-07,..Please let me know if I have missed any of the sqlplus command.

Thanks
Safeeq
Re: Column Formatting (merged) [message #347318 is a reply to message #347311] Thu, 11 September 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CAST your column in statement:
SQL> create table t (col varchar2(1000));

Table created.

SQL> insert into t values ('AAAAAAAAAA');

1 row created.

SQL> select substr(col,1,30) col from t;
COL
------------------------------
AAAAAAAAAA

1 row selected.

SQL> select cast(substr(col,1,30) as varchar2(10)) col from t;
COL
----------
AAAAAAAAAA

1 row selected.

Regards
Michel
Re: Column Formatting (merged) [message #347324 is a reply to message #347277] Thu, 11 September 2008 05:22 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Yes casting is the perfect solution.

SELECT DISTINCT CAST(SUBSTR(segment5,1,5) AS VARCHAR2(10)) "Prime", 
	   CAST(LTRIM(MAX(DECODE(SUBSTR(period_name,1,7),'AP01-08',cnt,0)))AS VARCHAR2(10)) "AP01-08", 
	   CAST(LTRIM(MAX(DECODE(SUBSTR(period_name,1,7),'AP02-08',cnt,0)))AS VARCHAR2(10)) "AP02-08", 
	   CAST(LTRIM(MAX(DECODE(SUBSTR(period_name,1,7),'AP03-08',cnt,0)))AS VARCHAR2(10)) "AP03-08", 
	   CAST(LTRIM(MAX(DECODE(SUBSTR(period_name,1,7),'AP04-08',cnt,0))) AS VARCHAR2(10)) "AP04-08",
	   CAST(SUM(cnt)AS VARCHAR2(10)) "Count"  
FROM exel_glr_cclines_tmp GROUP BY segment5 ORDER BY 1;

[Updated on: Thu, 11 September 2008 05:23]

Report message to a moderator

Re: Column Formatting (merged) [message #347358 is a reply to message #347277] Thu, 11 September 2008 06:17 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

It worked perfectly. Thanks for your help.
Previous Topic: How to Find Last DML on a table
Next Topic: Getting the function value with arguments by using execute immediate
Goto Forum:
  


Current Time: Wed Dec 07 16:51:32 CST 2016

Total time taken to generate the page: 0.08040 seconds