Query to turn columns into records

Asked

Viewed 431 times

1

I have a query that involves several tables in Oracle, and returns a large number of columns. I wanted instead of returning the records of this query, return me the columns of select 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 to no avail.

sql database oracle
  • 1

    See if it helps, http://answall.com/questions/104776/convers%C3%A3o-e-grouping-of-rows-in-columns-dynamically-in-oracle

  • 1

    These examples are not in Oracle, but may help you: 1 - http://answall.com/questions/135025/como-converter-resultado-de-searchno-mysql-em-colunas/135035#135035 2 - http://answall.com/questions/131780/como-transfomar-a-linha-de-a-table-in-column/131796#131796

  • 1

    Both helped. I managed to list something. Thank you.

  • @Alexandre, could add the solution as an answer to the problem?

  • SELECT COLUNA1 FROM TABELA1 UNION SELECT COLUNA2 FROM TABELA1 UNION ... SELECT COLUNAN FROM TABELA1 UNION SELECT COLUNA1 FROM TABELAN ,... UNION SELECT COLUNAN FROM TABELAN

1 answer

1


Found the following solution to solve 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
    )
)

It is returning some records. One problem that may occur is because of different column types. Then one resolution for this would be to use TO_CHAR in select to leave all columns as string.

If the normal query returns more than 1 row, it is possible to repeat 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 the columns of the query without duplication.

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

Browser other questions tagged sql database oracle

You are not signed in. Login or sign up in order to post.