Home » SQL & PL/SQL » SQL & PL/SQL » Convert Columns to Rows
Convert Columns to Rows [message #664642] Tue, 25 July 2017 17:03 Go to next message
SwathiKarre
Messages: 2
Registered: July 2017
Junior Member
Hi,

I am looking for creating view, which provides the column as row value.

For example --

I have a table say table1 having columns ID,column1,column2,column3 with values as below

Table1
ID Column1 Column2 Column3

1 value1 value2
2 value3

Result what I am looking for is below --

ID column_name column_value
1 column1 value1
1 column2 value2
2 column3 value3

Please let me know how can I achieve this?


Re: Convert Columns to Rows [message #664643 is a reply to message #664642] Tue, 25 July 2017 17:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
select id, 'column1' as column_name, column1 as column_value from tabl1 where column1 is not null
union all
select id, 'column2' as column_name, column2 as column_value from tabl1 where column2 is not null
.......
Re: Convert Columns to Rows [message #664664 is a reply to message #664643] Wed, 26 July 2017 12:38 Go to previous messageGo to next message
SwathiKarre
Messages: 2
Registered: July 2017
Junior Member
I have around 40 tables to be used, is there any other way to get this??
Re: Convert Columns to Rows [message #664665 is a reply to message #664664] Wed, 26 July 2017 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SwathiKarre wrote on Wed, 26 July 2017 10:38
I have around 40 tables to be used, is there any other way to get this??

other than writing SQL, how do you propose to convert columns to rows by Oracle?
Re: Convert Columns to Rows [message #664674 is a reply to message #664664] Thu, 27 July 2017 03:15 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
SwathiKarre wrote on Wed, 26 July 2017 19:38
I have around 40 tables to be used, is there any other way to get this??
Around 40 tables or 40 columns?

If the latter, you may use UNPIVOT operation,as described e.g. here: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 , as the query is more concise.
There is also a sample query for pre-11g databases on that page.
You still have to state all column names - otherwise how would Oracle know which columns shall be used?
Re: Convert Columns to Rows [message #664698 is a reply to message #664642] Fri, 28 July 2017 17:06 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
SQL to Generate SQL: (Dynamic method for UNPIVOT)

Utilizing the System Dictionary Table(s) and the "LISTAGG" Function you can produce some defined variable(s)
to help build some more flexible/dynamic SQL that can be used to meet your requirements. The following is
a script that can be called passing 2 parameters, the table name and the primary key column (If more than
1 PK Column separate the columns with a comma(s).) The script is also setup to handle the case of the
passed parameters changing them to uppercase in the SQL statements. Also the script generates

Also some logic was required to handle the "NUMBER" and "DATE" Data Types because the expected output
for the "COLUMN_VALUE" Column is a Character Data Type and processing tables with the mentioned data types
raised errors.

You may have to tweak the scripts some to meet your requirements for the additional tables mentioned in the Post.

You may have to make modifications to the scripts in order to meet your requirements for the additional
tables mentioned in the Post.

SYNTAX:     @tabrpt {table name} {PK column name}

SAMPLE:     @tabrpt table1 id

-- Defined Variables

DEFINE LV_PK_NAME      = "id" (CHAR)
DEFINE LV_PK_IN_LIST   = "ID" (CHAR)
DEFINE LV_CL_LIST      = "TO_CHAR(ID) AS ID,COLUMN1,COLUMN2,COLUMN3" (CHAR)
DEFINE LV_UP_IN_LIST   = "COLUMN1,COLUMN2,COLUMN3" (CHAR)

-- Script Call and Output:

SQL> @tabrpt  table1  id

Table Name:  TABLE1

ID                                       COLUMN_NAME                    COLUMN_VALUE
---------------------------------------- ------------------------------ ------------------------------
1                                        COLUMN1                        value1
1                                        COLUMN2                        value2
2                                        COLUMN3                        value3

3 rows selected.


-- Script tabrpt.sql

SET FEEDBACK  off;
SET HEADING   off;
SET LINESIZE  4000;
SET LONG      4000;
SET LONGCHUNK 4000;
SET TRIMSPOOL on;
SET VERIFY    off;
SET TERMOUT   off;

-- Setup and assignment script variables.
CLEAR COLUMNs;
UNDEFINE lv_pk_name;
UNDEFINE lv_pk_in_list;
UNDEFINE lv_cl_list;
UNDEFINE lv_up_in_list;
COLUMN lv_pk_name    new_value lv_pk_name;
COLUMN lv_pk_in_list new_value lv_pk_in_list;
COLUMN lv_cl_list    new_value lv_cl_list;
COLUMN lv_up_in_list new_value lv_up_in_list;

SELECT '&2' AS lv_pk_name
FROM   DUAL;

SELECT REPLACE(UPPER('&2'),',', chr(39)||','||CHR(39)) AS lv_pk_in_list
FROM   DUAL;

SELECT   LISTAGG(CASE data_type
                   WHEN 'NUMBER' THEN 'TO_CHAR('||column_name || ') AS ' || column_name
                   WHEN 'DATE'   THEN 'TO_CHAR('||column_name
                                                || ','
                                                || CHR(39)
                                                || 'MM/DD/YYYY HH24:MI:SS'
                                                || CHR(39)
                                                ||') AS '
                                                || column_name
                                 ELSE column_name
                 END, ',')
         WITHIN GROUP (ORDER BY column_id) AS lv_cl_list 
FROM    (SELECT column_id, data_type, column_name
         FROM   user_tab_columns
         WHERE  table_name = UPPER('&1'));

SELECT   LISTAGG(column_name, ',')
         WITHIN GROUP (ORDER BY column_id) AS lv_up_in_list 
FROM    (SELECT column_id,column_name
         FROM   user_tab_columns
         WHERE  table_name = UPPER('&1')
         AND    column_name NOT IN ('&lv_pk_in_list'));

-- Report Heading
SET TERMOUT   on;

SELECT 'Table Name:  ' || UPPER('&1') FROM DUAL;

SET HEADING   on;
SET FEEDBACK  on;

COLUMN column_name  FORMAT A30;
COLUMN column_value FORMAT A30 WRAPPED;

SELECT   &lv_pk_name
        ,col       AS column_name 
        ,val       AS column_value
FROM    (SELECT &lv_cl_list FROM &1)
UNPIVOT (val FOR col IN (&lv_up_in_list));



-- Script Example of a Multiple Column Primary Key and NUMBER and DATE Data Types:

-- Table Setup

DROP TABLE table2;

CREATE TABLE table2
( 
  col1_pk    NUMBER(10)
 ,col2_pk    VARCHAR2(10)
 ,col3_nbr   NUMBER
 ,col4_date  DATE
 ,col5_vrchr VARCHAR2(100)
);

ALTER TABLE table2 ADD CONSTRAINT table2_pk PRIMARY KEY (col1_pk,col2_pk);

INSERT INTO table2 VALUES (1, 'A', 199, TO_DATE('01-01-2017 20:10:00', 'mm-dd-yyyy hh24:mi:ss'), 'ABCDEF');
INSERT INTO table2 VALUES (2, 'B',  88, TO_DATE('07-01-2017 10:10:00', 'mm-dd-yyyy hh24:mi:ss'), 'Sample Text');
COMMIT;


-- Defined Variables

DEFINE LV_PK_NAME      = "col1_pk,col2_pk" (CHAR)
DEFINE LV_PK_IN_LIST   = "COL1_PK','COL2_PK" (CHAR)
DEFINE LV_CL_LIST      = "TO_CHAR(COL1_PK) AS COL1_PK,COL2_PK,TO_CHAR(COL3_NBR) AS COL3_NBR,TO_CHAR(COL4_DATE,'MM/DD/YYYY HH24:MI:SS') AS COL4_DATE,COL5_VRCHR" (CHAR)
DEFINE LV_UP_IN_LIST   = "COL3_NBR,COL4_DATE,COL5_VRCHR" (CHAR)


-- Script Call and Output:

SQL> @tabrpt  table2  col1_pk,col2_pk

Table Name:  TABLE2

COL1_PK                                  COL2_PK    COLUMN_NAME                    COLUMN_VALUE
---------------------------------------- ---------- ------------------------------ ------------------------------
1                                        A          COL3_NBR                       199
1                                        A          COL4_DATE                      01/01/2017 20:10:00
1                                        A          COL5_VRCHR                     ABCDEF
2                                        B          COL3_NBR                       88
2                                        B          COL4_DATE                      07/01/2017 10:10:00
2                                        B          COL5_VRCHR                     Sample Text

6 rows selected.



-- Example SQL to Generate SQL (Creates a List of calls to the tabrpt.sql fro all tables in the schema)
[This is an example (Not Intended to be functional and not pretty) of how the System Dictionary Views/Tables
can be used to create SQL commands from SQL script.]

SELECT   '@tabrpt  '
       || table_name
       || '  '
       || col_lst       AS list
FROM     (SELECT    uc.table_name                         AS table_name
                   ,LISTAGG(ucc.column_name, ',')
                    WITHIN GROUP (ORDER BY ucc.position)  AS col_lst
          FROM      user_constraints  uc
                   ,user_cons_columns ucc
          WHERE     uc.constraint_name = ucc.constraint_name
          AND       uc.constraint_type = 'P'
          GROUP BY  uc.table_name
         )
          ORDER BY  table_name;


-- Script Output which can be spooled or cut/paste to a file to be run to process all tables meeting the
where criteria. This example is assuming that every table has a Primary Key Constrain and the script
could be modified as needed to produce the needed output.

LIST
--------------------------------------------------------------------------------
@tabrpt  DEPT  DEPTNO
@tabrpt  EMP  EMPNO
@tabrpt  TABLE1  ID
@tabrpt  TABLE2  COL1_PK,COL2_PK
Re: Convert Columns to Rows [message #664709 is a reply to message #664642] Sun, 30 July 2017 07:48 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
I'll assume PK is always single column key:

with t as (
           select  to_clob('select * from ') || tc.owner || '.' || tc.table_name ||
                   ' unpivot(column_value for column_name in (' ||
                   listagg(tc.column_name,',')
                      within group(order by tc.column_id) || '))' stmt
             from  dba_tab_columns tc
             where tc.owner = '&TABLE_OWNER'
               and tc.table_name = '&TABLE_NAME'
               and tc.column_name not in (
                                          select  cc.column_name
                                            from  dba_constraints c,
                                                  dba_cons_columns cc
                                            where c.owner = tc.owner
                                              and c.table_name = tc.table_name
                                              and c.constraint_type = 'P'
                                              and cc.owner = c.owner
                                              and cc.constraint_name = c.constraint_name
                                         )
             group by tc.owner,
                      tc.table_name
          )
select  x.*
  from  t,
        xmltable(
                 '/ROWSET/ROW'
                 passing dbms_xmlgen.getxmltype(stmt)
                 columns
                   &PK_COLUMN   number path '&PK_COLUMN',
                   column_name  varchar2(30) path 'COLUMN_NAME',
                   column_value varchar2(&COLUMN_VALUE_MAX_LEN) path 'COLUMN_VALUE'
                ) x
/
Enter value for table_owner: SCOTT
Enter value for table_name: DEPT
Enter value for pk_column: DEPTNO
Enter value for pk_column: DEPTNO
Enter value for column_value_max_len: 30

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

8 rows selected.

SQL>

SY.
P.S. You will need to adjust STMT generation to avoid implicit conversions if any of your columns are dates/timestamps.

[Updated on: Sun, 30 July 2017 07:55]

Report message to a moderator

Previous Topic: Count , Per Week Group by
Next Topic: string aggregation
Goto Forum:
  


Current Time: Fri Mar 29 01:22:55 CDT 2024