Forum Stats

  • 3,853,194 Users
  • 2,264,189 Discussions
  • 7,905,283 Comments

Discussions

Pivot a Single Row of Data

3234063
3234063 Member Posts: 12
edited May 8, 2016 4:34PM in SQL & PL/SQL

I need to know how to pivot a single row of data.

I have a select statement that will always returns a single row of data with about 100 columns. I need to return instead a single column that has 100 rows. It is critical that I have the rows returned in an order controllable by the column names.

with t as (select 'a' as c1, 'x' as c2, 'm' as c3, 'w' as c4 from dual)

select * from t

I need to produce the following:

a

x

m

w

The following is NOT acceptable

a

m

w

x

Thanks,

Scott

Tagged:
KalpataruCarlosDLG

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited May 8, 2016 7:46AM Answer ✓

    with t as (

               select 'a' as c1, 'x' as c2, 'm' as c3, 'w' as c4 from dual

              )

    select  val

      from  t

      unpivot include nulls (val for ord in(c1 as 1,c2 as 2,c3 as 3,c4 as 4))

      order by ord

    /

    V

    -

    a

    x

    m

    w

    SQL>

    SY.

    Kalpataru3234063

Answers

  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited May 8, 2016 3:54AM

    WITH t AS

       (SELECT 'a' AS c1, 'x' AS c2, 'm' AS c3, 'w' AS c4

       FROM DUAL)

    SELECT REGEXP_SUBSTR (c1 || '|' || c2 || '|' || c3 || '|' || c4, '[^\|]+', 1, LEVEL) "RESULT"

       FROM t

    CONNECT BY LEVEL <= regexp_count (c1 || '|' || c2 || '|' || c3 || '|' || c4, '[^\|]+')


    RESULT

    a
    x
    m
    w

    Kalpataru3234063
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited May 8, 2016 4:16AM

    in case you are having multiple rows, use the query below.

    WITH t AS

       (SELECT 'a' AS c1, 'x' AS c2, 'm' AS c3, 'w' AS c4

        FROM DUAL UNION ALL

        SELECT 'e' AS c1, 'f' AS c2, 'g' AS c3, 'h' AS c4

        FROM DUAL UNION ALL

        SELECT 'i' AS c1, 'j' AS c2, 'k' AS c3, 'l' AS c4

        FROM DUAL),

    temp12 AS

       (SELECT c1 || '|' || c2 || '|' || c3 || '|' || c4 col1, ROWNUM RANK

        FROM t)

    SELECT   REGEXP_SUBSTR (col1, '[^\|]+', 1, LEVEL, 'i') "RESULT"

    FROM temp12

    CONNECT BY LEVEL <= regexp_count (col1, '\|') + 1

       AND PRIOR RANK = RANK

       AND PRIOR SYS_GUID () IS NOT NULL

    RESULT

    a
    x
    m
    w
    e
    f
    g
    h
    i
    j
    k
    l

    Thank and Regards,

    Vysakh Suresh

    Kalpataru
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited May 8, 2016 7:46AM Answer ✓

    with t as (

               select 'a' as c1, 'x' as c2, 'm' as c3, 'w' as c4 from dual

              )

    select  val

      from  t

      unpivot include nulls (val for ord in(c1 as 1,c2 as 2,c3 as 3,c4 as 4))

      order by ord

    /

    V

    -

    a

    x

    m

    w

    SQL>

    SY.

    Kalpataru3234063
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited May 8, 2016 8:16AM

    What initial table? Query returns 1 row with N columns. OP wants to show N rows with 1 column instead and wants rows to be returned in column order. Typical unpivot. It can be done using UNPIVOT, hierarchical query, UNION ALL...

    SY.

  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    edited May 8, 2016 2:34PM

    You can use UNPIVOT if it is available in the database version you are using (which you didn't mention), as shown by Solomon.

    If you don't want to have to assign an alias for each unpivoted column or having to rely on those aliases for the ordering, you can join the unpivoted results to the all_tab_columns dictionary view to order by column_id:

    SELECT up.val
    FROM t UNPIVOT INCLUDE NULLS (val FOR col IN(c1, c2, c3, c4)) up
    JOIN all_tab_columns atc
    ON up.col = atc.column_name
      AND owner = 'TEST'
      AND atc.table_name = 'T'
    ORDER BY atc.column_id;
    

    In this example 'TEST' is the name of the owner or schema of the table and 'T' is the table name.

    To test it you need to have the actual table created. It will not work with a CTE (the subquery in the with clause) as the one you used to explain the requirement.

    3234063
  • 3234063
    3234063 Member Posts: 12
    edited May 8, 2016 4:34PM

    I appreciate everyone's help. This is my first time using this forum and was very impressed with the quality and variety of the answers.

    CarlosDLG
This discussion has been closed.