SQL - Consolidate and group records from multiple rows to columns-CodePudding

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.

CodePudding user response：

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' )
    );