Home » SQL & PL/SQL » SQL & PL/SQL » Convert Columns to Rows
Convert Columns to Rows [message #664642] |
Tue, 25 July 2017 17:03 |
|
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 |
cookiemonster
Messages: 13925 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 #664698 is a reply to message #664642] |
Fri, 28 July 2017 17:06 |
|
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 |
Solomon Yakobson
Messages: 3275 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
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:30:56 CDT 2024
|