Transform one row for columns where there is same data in other columns

user_14321574 0
Viewed 66

Long time have visited this site and got lot of help. Now I could not find what I'm looking for. I'm working on Oracle SQL database

Table

columnA ColumnB ColumnC
 10A       AA      00
 10A       AA      10
 10A       AA      20
 10A       AA      30
 10A       BB      00
 10A       BB      10
 10A       BB      20
 10A       BB      30

and what I would like to do is extend the columns where there is same data in ColumnA and B like:

columnA ColumnB ColumnC ext.ColumnD ext.ColumnE ext.ColumnF
 10A       AA      00      10           20         30
 10A       BB      00      10           20         30

I have tried different pivots, tried lot of suggestions I've found forums but just cant get the result I hope for.

This is my first post so sorry in advance if there is something wrong or information is missing.

Thank you so much in advance!

/ edit

And there would not be same data in ColumnC always or even as many differents. If BB would not have 10 and have differents, it should look something like this:

columnA ColumnB ColumnC ext.ColumnD ext.ColumnE ext.ColumnF
 10A       AA      00      10           20         30
 10A       BB      00      17           47

Also goal is to get them increasing order, but that can be done with query, sorting in the first place.

/Edit

Now that I have this one table:

ColumnA ColumnB ColumnC Height1 Height2 Height3 height4
5A        11       A       0       25       50     75
5A        11       B       0       25       50     75
5A        11       C       0       25       50  
5A        11       D       0       25       50     75

I have one row in the original table that have random digits which are unique and it needs to go after the height. pivot table with unique row:

ColumnA     ColumnB     ColumnC     Unique      ColumnE     ColumnF     ColumnG ColumnG
5A            11           A        16805097      00                            
5A            11           A        62366527                    25                      
5A            11           A        65672596                            75              
5A            11           A        68078617                                       50

So results would be:

ColumnA ColumnB ColumnC Height1 Unique1 Height2 Unique2 Height3 Unique3 Height4 Unique4
5A       11      A       00     16805097  25    62366527  75    65672596  50   68078617

So if we would take the original question's table, the start point would be like this:

columnA ColumnB ColumnC ColumnD
 10A       AA      00    699787
 10A       AA      10    439567
 10A       AA      20    429456
 10A       AA      30    122172
 10A       BB      00    133244
 10A       BB      10    328311
 10A       BB      20    247422
 10A       BB      30    769636

Br Jan

oracle sql
2 Answers

You can use PIVOT as well and use it we can use row_number() to give a unique number by partition columnA and columnB and then use it in the pivoting clause.

The only disadvantage as you might be aware of is to hard code the values in the PIVOT clause. I have taken max 10 values as of now which you can adjust as per your need.

Note:- The column names generated by PIVOT will be in format of e.g. COLUMND,COLUMNE and so on.. which further can be changed at outer most query as you want. You can also give an alias to MAX(columnC) some_alias but then the columns names will be append it like COLUMND_SOME_ALIAS,COLUMNE_SOME_ALIAS and so on..

SELECT *
  FROM
(
SELECT columnA,columnB,columnC,
       row_number() OVER (PARTITION BY columnA,columnB ORDER BY columnC) rn_pivot
  FROM table1
)
PIVOT
(
 MAX(columnC)
 FOR rn_pivot IN (1 AS columnD
                 ,2 AS columnE
                 ,3 AS columnF
                 ,4 AS columnG
                 ,5 AS columnH
                 ,6 AS columnI
                 ,7 AS columnJ
                 ,8 AS columnK
                 ,9 AS columnL
                 ,10 AS columnM)
);
user_138780230

If you combine the values in ColumnC into a single CSV list using LISTAGG, you can then use REGEXP_SUBSTR to get the the values in that CSV list. The example below returns up to 5 values for each combination of ColumnA and ColumnB but you can add additional REGEXP_SUBSTR columns if more than 5 are needed.

Query

WITH
    sample_data (cola, colb, colc)
    AS
        (SELECT '10A', 'AA', '00' FROM DUAL
         UNION ALL
         SELECT '10A', 'AA', '10' FROM DUAL
         UNION ALL
         SELECT '10A', 'AA', '20' FROM DUAL
         UNION ALL
         SELECT '10A', 'AA', '30' FROM DUAL
         UNION ALL
         SELECT '10A', 'BB', '00' FROM DUAL
         UNION ALL
         SELECT '10A', 'BB', '10' FROM DUAL
         UNION ALL
         SELECT '10A', 'BB', '20' FROM DUAL
         UNION ALL
         SELECT '10A', 'BB', '30' FROM DUAL
         UNION ALL
         SELECT '10A', 'BB', '40' FROM DUAL)
SELECT cola,
       colb,
       REGEXP_SUBSTR (colc_vals, '[^,]+', 1, 1) as colc_1,
       REGEXP_SUBSTR (colc_vals, '[^,]+', 1, 2) as colc_2,
       REGEXP_SUBSTR (colc_vals, '[^,]+', 1, 3) as colc_3,
       REGEXP_SUBSTR (colc_vals, '[^,]+', 1, 4) as colc_4,
       REGEXP_SUBSTR (colc_vals, '[^,]+', 1, 5) as colc_5
  FROM (  SELECT cola, colb, LISTAGG (colc, ',') within group (order by colc) AS colc_vals
            FROM sample_data
        GROUP BY cola, colb);

Result

   COLA    COLB    COLC_1    COLC_2    COLC_3    COLC_4    COLC_5
_______ _______ _________ _________ _________ _________ _________
10A     BB      00        10        20        30        40
10A     AA      00        10        20        30
user_71100990
Related