Need query to get ID wise value for each person

Question

Possible Duplicate:
Oracle SQL pivot query

I have a table structure as follows

P_id    P_name  P_value
123         chetan  34
123         john    83
123         paul    38
273         chetan  54
273         john    24
273         paul    34

need output as follows.

Pid chetan  john    paul
123    34   83      38
273    54   24      34

Please suggest me the query.

Can you please **EXPLAIN** the logic behind what you want in the output!?!?!? Don't just throw piles of data at us and let us figure it out .... — marc_s, Oct 16 '12 at 16:46
Do you know at compile time that there are exactly 3 distinct `p_name` values per `p_id`? Is there a maximum number of `p_name` values per `p_id` that you could use in order to do a static pivot of the data? Or do you need the number of columns in the result to vary? If so, that would require dynamic SQL. What language/ framework/ API would you be using to generate and execute that dynamic SQL? — Justin Cave, Oct 16 '12 at 16:50
Typically this is done in the presentation layer, using perl, java, python, ruby, crystal reports, etc. — Marlin Pierce, Oct 16 '12 at 16:50
I want to calculate the project id wise value given to each person. — user1644154, Oct 16 '12 at 16:50

Taryn · Answer 1 · 2012-10-16T16:56:12.497

Since you are on Oracle 10g, there is no PIVOT function. So you can replicate it using a CASE statement with an aggregate function. If you know the values that you want as columns, then you can hard-code it:

select p_id,
  max(case when p_name = 'chetan' then p_value end) chetan,
  max(case when p_name = 'john' then p_value end) john,
  max(case when p_name = 'paul' then p_value end) paul
from table1
group by p_id

See SQL Fiddle with Demo

If you do not know the values, then you will want to use a dynamic version of this, similar to this:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select P_id ';

    begin
        for x in (select distinct P_name from table1 order by 1)
        loop
            sql_query := sql_query ||
              ' , max(case when P_name = '''||x.P_name||''' then P_value end) as '||x.P_name;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from Table1 group by P_id';

        open p_cursor for sql_query;
    end;
/

Then to execute it, you can use:

variable x refcursor
exec dynamic_pivot(:x)
print x

The results would be:

      P_ID     CHETAN       JOHN       PAUL
---------- ---------- ---------- ----------
       123         34         83         38
       273         54         24         34

Are you manually typing out the example result table? If so, I should point out that you can save a bit of time by using the "plaintext output" format option on SQL Fiddle. You can choose the output format by clicking the dropdown arrow next to the "Run SQL" button. — Jake Feasel, Oct 16 '12 at 17:17
@JakeFeasel those results were from toad. :) But thanks for that info, I was not aware of that feature. — Taryn, Oct 16 '12 at 17:18

Need query to get ID wise value for each person

1 Answers1