This discussion is archived
2 Replies Latest reply: Oct 18, 2012 11:47 AM by 91708 RSS

Display multiple values in to a single cell

idmr2 Newbie
Currently Being Moderated
Hi friends,
I have a problem with displaying the data in my table.

My table contains following data,

Id role rank
1 H.R 2
1 T.L 1
2 P.M 1
2 Manager 2
..............

like above i have my data....
now i want to display my data like as follows,

id role rank
1 H.r , T.L 1,2
2 P.M , Manager 1,2
......................

like the above scenario i have to display multivalued attributes into a single cell based on 'id'

...Help Me to solve this problem

Thanks in advance,
Nani

Edited by: 962874 on Oct 3, 2012 3:44 AM

Edited by: 962874 on Oct 3, 2012 3:45 AM
  • 1. Re: Display multiple values in to a single cell
    rp0428 Guru
    Currently Being Moderated
    >
    i have to display multivalued attributes into a single cell based on 'id'
    >
    SQL Developer is going to display whatever the cell value is to if you want to display what you consider to be multiple data items you need to convert them to a single data item and then they will display in a single cell.

    So you need to write a query to convert the rows to columns; a topic that comes up so often it has its own FAQ.

    Since this is not a SQL Developer question it should be posted in the SQL and PL/SQL forum
    SQL and PL/SQL

    Please mark this question ANSWERED and repost it in the other forum.

    Before you post there read the FAQ #4: How do I convert rows to columns.
    SQL and PL/SQL FAQ
  • 2. Re: Display multiple values in to a single cell
    91708 Newbie
    Currently Being Moderated
    [http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php|http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php]
    See the preceding link to see examples of doing this...

    Here's an example...
    SELECT deptno,
    LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
    KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
    FROM (SELECT deptno,
    ename,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
    FROM emp)
    GROUP BY deptno
    CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
    START WITH curr = 1;

    DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
    10 CLARK,KING,MILLER
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

    3 rows selected.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points