SQL Pivot to Return Distinct Values as Column Name

Question

I have a table column with this structure:

|------ ID ------|
|-  1.20.10.00  -|
|-  1.20.10.10  -|
|-  1.20.10.20  -|
|-  1.20.20.00  -|
|-  1.20.20.10  -|
|-  1.40.10.00  -|
|-  1.40.20.00  -|
|-  1.60.10.00  -|
|-  1.60.10.00  -|

I am trying to run a query which will pivot the data into multiple columns based on the Distinct values returned by a string Like the 5 left characters in the value, the column names matching the 5 characters used in the like statement. Let me show an example of what I want to get to:

|----- 1.20. ----||----- 1.40. ----||----- 1.60. ----|
|-  1.20.10.00  -||-  1.40.10.00  -||-  1.60.10.00  -|
|-  1.20.10.10  -||-  1.40.20.00  -||-  1.60.10.00  -|
|-  1.20.10.20  -|
|-  1.20.20.00  -|
|-  1.20.20.10  -|

I'm on an Oracle 11g db, so I figured I should be using the PIVOT command, but I can't figure out how to set it up with the addition of the DISTINCT and LIKE commands. Any help would be appreciated.

Nick Krasnov · Answer 1 · 2013-11-01T21:15:19.840

As option number one you can use combination of row_number() over() analytic function, max() aggregate function and case expression:

select max(case when substr(col, 1, 4) = '1.20' then col end) as "1.20"
     , max(case when substr(col, 1, 4) = '1.40' then col end) as "1.40"
     , max(case when substr(col, 1, 4) = '1.60' then col end) as "1.60"
 from (select col
            , row_number() over(partition by substr(col, 1, 4) 
                                    order by substr(col, 1, 4)) as rn
        from t1)
group by rn

Result:

1.20       1.40       1.60     
---------- ---------- ----------
1.20.10.00 1.40.10.00 1.60.10.00 
1.20.10.10 1.40.20.00 1.60.10.00 
1.20.20.00                       
1.20.20.10                       
1.20.10.20

Note: Not a good choice of column aliases thought.

As another option, you can use, introduced in Oracle 11g version, pivot operator:

select "1.20"
     , "1.40"
     , "1.60"
       from (select col
                  , substr(col, 1, 4) as common_part
                  , row_number() over(partition by substr(col, 1, 4) 
                                          order by substr(col, 1, 4)) as rn
              from t1)
pivot(
  max(col) for common_part in ( '1.20' as "1.20"
                              , '1.40' as "1.40"
                              , '1.60' as "1.60")
)

Result:

1.20       1.40       1.60     
---------- ---------- ----------
1.20.10.00 1.40.10.00 1.60.10.00 
1.20.10.10 1.40.20.00 1.60.10.00 
1.20.20.00                       
1.20.20.10                       
1.20.10.20

Thanks for the reply. I like the second option, however, the potential values in the column could change. I was just reading over the Oracle doc for the pivot command [link](http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html) which states "This line [for,in] is necessary, so unfortunately you have to know the possible values beforehand. This restriction is relaxed in the XML format of the query, described later in this article." This seems to limit my ability to use the pivot command, and XML is not a reasonable option. I'm going to keep working at it. Thanks. — thefreeline, Nov 01 '13 at 20:44

SQL Pivot to Return Distinct Values as Column Name

1 Answers1