0

I have a table like below.

USERID USERNAME ITEM
12345 JESSE ITEM1
12345 JESSE ITEM3
12345 DON ITEM2
12346 DANNY ITEM4
12346 ELISA ITEM1
12347 ANDY ITEM2
12347 ANDY ITEM4

What im trying to achieve is.

USERID USERNAME ITEMS1 ITEMS2
12345 JESSE ITEM1 ITEM3
12345 DON ITEM2
12346 DANNY ITEM4
12346 ELISA ITEM1
12347 ANDY ITEM2 ITEM4

I've tried to use LISTAGG but cant get resuls like above. Or should i use something else?

Thanks!

Eliza

Edit: with SQLFiddle i tried with this but end up with error: http://sqlfiddle.com/#!4/fb69a/45/0

Edit: Found similiar dynamic pivot examples, but still cant get this working :/ https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238 SQLFiddle http://sqlfiddle.com/#!4/51d2d/20/0

Schema:

CREATE TABLE Table1

(IDENT varchar2(10), USERNAME varchar2(10), ITEMNAME varchar2(10))
;

INSERT ALL 
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12345','JESSE','ITEM1')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12345','JESSE','ITEM3')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12346','DON','ITEM2')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12346','DANNY','ITEM4')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12347','ELISA','ITEM1')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12347','ANDY','ITEM2')
    INTO Table1 (IDENT,USERNAME,ITEMNAME)
         VALUES ('12347','ANDY','ITEM4')
SELECT * FROM dual
;

Code:

create or replace procedure
get_data(p_x out sys_refcursor)
as
    l_query varchar2(400) :='IDENT';
    begin
        for x in (select distinct USERNAME from Table1 order by 1)
        loop
            l_query := l_query ||replace(', sum(decode(USERNAME,''$X'',ITEMNAME)) as $X '
         ,'$X',x.USERNAME );
        end loop;
    l_query := l_query ||' from Table1 group by IDENT ';
    open p_x for l_query;
end;
/

exec get_data(:x);
print x;
5
  • 1
    The term you're looking for is "pivot".
    – mustaccio
    Sep 8 at 12:09
  • Ahh ok ill try to get more information about pivot then. This is all new to me :)
    – Eliza
    Sep 8 at 13:33
  • I cant figure out how to solve this with pivot either, because i don't know any values in that table besides their column names (USERID,USERNAME,ITEMS). Can anyone please help me on this?
    – Eliza
    Sep 10 at 12:56
  • 2
    Does this answer your question? Pivot rows into multiple columns
    – mustaccio
    Sep 10 at 12:59
  • Thanks! I've tried to understand that code and modified it but end up with error. ORA-06550: line 8. Not sure if its that SQL Fiddle because i cant get to my DB until monday.I will try it then.
    – Eliza
    Sep 11 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.