Is there a way to transpose multiple rows and group them to columns?
I have a table that has data from Invoice values from different tables and columns. Now, I want to have them by Invoice numbers.
InvoiceNumber | Table | Column | Value |
---|---|---|---|
123 | A | Column A1 | ABC |
123 | A | Column A2 | DEF |
123 | A | Column A3 | GHI |
123 | B | Column B1 | JKL |
123 | B | Column B2 | MNO |
234 | A | Column A1 | 1BC |
234 | A | Column A2 | 2EF |
234 | A | Column A3 | 3HI |
Now, I need to have the details of the Invoice numbers consolidated in the following way.
InvoiceNumber | ColumnA1 | ColumnA2 | ColumnA3 | ColumnB1 | ColumnB2 |
---|---|---|---|---|---|
123 | ABC | DEF | GHI | JKL | MNO |
234 | 1BC | 2EF | 3HI |
Would greatly appreciate any help on this. I tried a few options with group by but none of them were effective.
Advertisement
Answer
Here is a potential solution using pivot. first create a table
CREATE TABLE TABLE1 ( INVOICENUMBER VARCHAR2(20), TABLE_NAME VARCHAR2(20), COLUMN_VAL VARCHAR2(20), VALUE VARCHAR2(20) )
lets insert some rows
REM INSERTING into TABLE1 SET DEFINE OFF; Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('123','A','Column A1','ABC'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('123','A','Column A2','DEF'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('123','A','Column A3','GHI'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('123','B','Column B1','JKL'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('123','B','Column B2','MNO'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('234','A','Column A1','1BC'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('234','A','Column A2','2EF'); Insert into TABLE1 (INVOICENUMBER,TABLE_NAME,COLUMN_VAL,VALUE) values ('234','A','Column A3','3HI');
now lets query those results with a pivot.
WITH cte_mytable AS ( SELECT invoicenumber, column_val, value FROM table1 ) SELECT * FROM cte_mytable PIVOT ( MAX ( value ) FOR column_val IN ( 'Column A1', 'Column A2', 'Column A3', 'Column B1', 'Column B2' ) );