CODEWITHSUNDEEP
sqloracle-databasepivot
krash
krash

Reputation: 25

Transpose of Query Result

We have a requirement where we need to transpose the query results .The query result is something like below.

Accnt   Country Weight
Acct1   US  55
Acct1   GB  45
Acct2   GB  35
Acct2   US  65

The output need to be transposed like below.

    Acct1   Acct2
US  55  65
GB  45  35

I have tied declare block ,but this query will be used in Dot net framework where they cant use declare block.So request the experts let me know possible ways to do this in single query ,both initial and the transpose.The accounts will be dynamic .

Upvotes: 0

Views: 449

Answers (2)

Dmitry Grekov
Dmitry Grekov

Reputation: 688

PIVOT is what you need, but there's also another option.

select  Country,
        max(case when Accnt = 'Acct1' then Weight end) Acct1,
        max(case when Accnt = 'Acct2' then Weight end) Acct2
from    test
group   by Country;

Upvotes: 1

Srini V
Srini V

Reputation: 11355

You can use PIVOT

select *
from (select Accnt,country,weight from test)
pivot (max(weight) for Accnt IN ('Acct1','Acct2'));

COUNTRY 'Acct1' 'Acct2'
US  55  65
GB  45  35

Upvotes: 1

Related Questions