CODEWITHSUNDEEP
sqloracle11gpivot
Jemo
Jemo

Reputation: 117

pivot/unpivot statement

Hi, having table like this

NR PROJECT  RESULT
1  name1    234
1  name2    937
2  name1    3
2  name2    6

I'm trying to have results presented this way:

NR NAME1 NAME2
1  234   937
2  3     6

so the VALUES of the PROJECT column become headers presenting respective RES value while the same NR values are merged into a single row

help :)

Upvotes: 0

Views: 40

Answers (1)

MatBailie
MatBailie

Reputation: 86735

If you're working with a fixed list of projects, you can use (as you've mention in the title of your question) a pivot.

http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

SELECT
    *
FROM
    yourTable
PIVOT
(
    SUM(result)
    FOR project IN ('name1' AS NAME1, 'name2' AS NAME2)
)

This would give the same results as from a "conditional aggregate".

SELECT
    NR,
    SUM(CASE WHEN PROJECT = 'name1' THEN result END)   AS NAME1,
    SUM(CASE WHEN PROJECT = 'name2' THEN result END)   AS NAME2
FROM
    yourTable
GROUP BY
    NR

Examples : http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=19f31ce152f4f88c3d74e867016765d7

Upvotes: 1

Related Questions