This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,964 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

How to covert column names into rows in SQL?

User_CK8A6
User_CK8A6 Member Posts: 5 Green Ribbon
edited May 19, 2021 10:22AM in SQL & PL/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

Tagged:

Answers

  • User_CK8A6
    User_CK8A6 Member Posts: 5 Green Ribbon

    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?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond

    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.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond

    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.

  • User_DQ5B6
    User_DQ5B6 Member Posts: 1 Green Ribbon
    edited Nov 29, 2022 10:05PM

    I also want it to manage rows and columns in website sql. Thanks.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond

    And what is "site" sql?

    SY.

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    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?

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Nov 29, 2022 2:55PM

    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.