Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How to covert column names into rows in SQL?
I have following requirement from my user.
I have a table which has column col1, col2, col3 and col4. Now using SQL I want the output to be displayed as
Col1 Col2
col1 col3
col1 col4
Please suggest how can we achieve this?
Thanks
Answers
-
-
Actually PIVOT and UNPIVOT will not work in my case. if you see my problem statement then it requires that one column names to be converted into row and its value. Is it possible to achieve the same using SQL or not?
-
You could use driver table - single column table with N - 1 rows where N is number of columns in your table. For example, I'll use HR.DEPARTMENTS table which has 4 columns. And keep in mind you need to convert columns to same data type:
with driver as ( select level l from dual connect by level <= 3 ) select dept.department_id, case drvr.l when 1 then dept.department_name when 2 then to_char(dept.manager_id) else to_char(dept.location_id) end col from hr.departments dept, driver drvr order by dept.rowid, drvr.l / DEPARTMENT_ID COL ------------- ---------------------------------------- 10 Administration 10 200 10 1700 20 Marketing 20 201 20 1800 30 Purchasing 30 114 30 1700 40 Human Resources 40 203 40 2400 50 Shipping 50 121 50 1500 60 IT 60 103 60 1400 70 Public Relations 70 204 70 2700 80 Sales 80 145 80 2500 90 Executive 90 100 90 1700 100 Finance 100 108 100 1700 110 Accounting 110 205 110 1700 120 Treasury 120 120 1700 130 Corporate Tax 130 130 1700 140 Control And Credit 140 140 1700 150 Shareholder Services 150 150 1700 160 Benefits 160 160 1700 170 Manufacturing 170 170 1700 180 Construction 180 180 1700 190 Contracting 190 190 1700 200 Operations 200 200 1700 210 IT Support 210 210 1700 220 NOC 220 220 1700 230 IT Helpdesk 230 230 1700 240 Government Sales 240 240 1700 250 Retail Sales 250 250 1700 260 Recruiting 260 260 1700 270 Payroll 270 270 1700 81 rows selected. SQL>
SY.
-
UNPIVOT.
-
It will if you know how to use it:
with t as ( select rowid rid, department_id, department_name, to_char(manager_id) manager_id, to_char(location_id) location_id from hr.departments ) select department_id, col from t unpivot include nulls ( col for sort_order in (department_name as 'A',manager_id as 'B',location_id as 'C') ) order by rid, sort_order / DEPARTMENT_ID COL ------------- ---------------------------------------- 10 Administration 10 200 10 1700 20 Marketing 20 201 20 1800 30 Purchasing 30 114 30 1700 40 Human Resources 40 203 40 2400 50 Shipping 50 121 50 1500 60 IT 60 103 60 1400 70 Public Relations 70 204 70 2700 80 Sales 80 145 80 2500 90 Executive 90 100 90 1700 100 Finance 100 108 100 1700 110 Accounting 110 205 110 1700 120 Treasury 120 120 1700 130 Corporate Tax 130 130 1700 140 Control And Credit 140 140 1700 150 Shareholder Services 150 150 1700 160 Benefits 160 160 1700 170 Manufacturing 170 170 1700 180 Construction 180 180 1700 190 Contracting 190 190 1700 200 Operations 200 200 1700 210 IT Support 210 210 1700 220 NOC 220 220 1700 230 IT Helpdesk 230 230 1700 240 Government Sales 240 240 1700 250 Retail Sales 250 250 1700 260 Recruiting 260 260 1700 270 Payroll 270 270 1700 81 rows selected. SQL>
SY.
-
I also want it to manage rows and columns in website sql. Thanks.
-
And what is "site" sql?
SY.
-
You have a follow-up question, one and a half year later? What have you done since? And why did you change your user name?
-
You should start a new thread for this question. (If you think it would be helpful, you can include a link to this thread.) As always, post CREATE TABLE and INSERT statements for a little sample data, the exact results you want from that sample data, your requirements and your full database version.
