2 Replies Latest reply: Oct 18, 2012 1:47 PM by jameseperryjr RSS

    Display multiple values in to a single cell

    idmr2
      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
          >
          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
            jameseperryjr
            [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.