Query to transform columns into records

Question

Query to transform columns into records

Navigation

#1 by (1 votes)

1

I have a query that involves multiple tables in Oracle, and returns a large number of columns. I wanted to instead of returning the records from this query, I would return the select columns as records.

An example of how more or less the current query is:

SELECT
tab1.campo1 'tab1.campo1',
tab1.campo2 'tab1.campo2',
tab1.campo3 'tab1.campo3',
tab2.campo1 'tab2.campo1',
tab2.campo2 'tab2.campo2',
tab2.campo3 'tab2.campo3',
tab3.campo1 'tab3.campo1',
tab3.campo2 'tab3.campo2',
tab3.campo3 'tab3.campo3',
tab4.campo1 'tab4.campo1',
tab4.campo2 'tab4.campo2',
tab4.campo3 'tab4.campo3'
FROM
tabela1 tab1,
tabela2 tab2, 
tabela3 tab3,
tabela4 tab4
WHERE
tab1.campo2 = tab2.campo1
and tab2.campo3 = tab4.campo1
and tab1.campo3 = tab3.campo1

Again, I need to list these columns as rows.

I tried to use Unpivot, but without success.

sql oracle

asked by anonymous 01.07.2016 / 14:37

1 answer

Configure MySQL connection in eclipse Java does not compile .java [closed]

score 1 · Accepted Answer

The following workaround was found to resolve this problem and return the columns.

SELECT
    COLUNA, 
    VALOR 
FROM(
    SELECT
        tab1.campo1 'tab1.campo1',
        tab1.campo2 'tab1.campo2',
        tab1.campo3 'tab1.campo3',
        tab2.campo1 'tab2.campo1',
        tab2.campo2 'tab2.campo2',
        tab2.campo3 'tab2.campo3',
        tab3.campo1 'tab3.campo1',
        tab3.campo2 'tab3.campo2',
        tab3.campo3 'tab3.campo3',
        tab4.campo1 'tab4.campo1',
        tab4.campo2 'tab4.campo2',
        tab4.campo3 'tab4.campo3'
    FROM
        tabela1 tab1,
        tabela2 tab2, 
        tabela3 tab3,
        tabela4 tab4
    WHERE
        tab1.campo2 = tab2.campo1
        and tab2.campo3 = tab4.campo1
        and tab1.campo3 = tab3.campo1
)
UNPIVOT INCLUDE NULLS(
    VALOR FOR COLUNA IN 
    (
        tab1.campo1,
        tab1.campo2,
        tab1.campo3,
        tab2.campo3,
        tab2.campo2,
        tab2.campo3,
        tab3.campo1,
        tab3.campo2,
        tab3.campo3,
        tab4.campo1,
        tab4.campo2,
        tab4.campo3
    )
)

You are returning some records. One problem that can occur is because of different column types. So a resolution to this would be to use TO_CHAR in select to leave all the columns as string.

If the normal query returns more than 1 row, there may be a repeat of the columns in the result with unpivot. So it would be ideal to ensure that it does not return more than 1 record, so it will list all query columns without duplication.

This problem is only meant to be able to pick up the columns, with the normal select values being dispensable here.