Forum Stats

  • 3,741,235 Users
  • 2,248,398 Discussions
  • 7,861,694 Comments

Discussions

how to display empno in a single row instead of displaying it vertically

naveenhks
naveenhks Member Posts: 82
edited Nov 6, 2010 11:34PM in SQL & PL/SQL
Hi All,
Greetings!

Requirement :
A select statement to display empno horizontally instead of vertical display at sql prompt.
For Eg:

SQL> Select empno from emp;
empno
--------
9999
2222
3333
4444
5555
...
...
...
...

Instead want the display of data in the below format

empno
----------
9999,2222,3333,4444,5555..........

Thanks in advance.

Regards,
Naveen.

Comments

  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    edited Nov 11, 2005 3:16AM
    SQL> select deptno,
      2    replace( replace( replace(
      3      XMLQUERY('for $cc in ora:view("emp")
    let $ename:=$cc/ROW/ENAME/text()
    where $cc/ROW/DEPTNO/number()=$deptno/d/number()
    return <e>{$ename}</e>'
      4      passing by value xmltype('<d>'||deptno||'</d>') as "deptno"
      5      returning content
      6    ),'</e><e>', ','),'<e>'),'</e>') enames
      7  from dept
        DEPTNO ENAMES
    ---------- --------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
            40
    http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
  • naveenhks
    naveenhks Member Posts: 82
    Hi,
    Thanks for your response.

    I missed out to specify my database version. I am working on below specified version:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    PL/SQL Release 8.1.7.4.0 - Production

    and moreover without using dept table is it possible to display all the employee names in single line with "," as delimiter.

    like a sql query which displays all the employee names present in employee table in a single string with "," as delimiter.

    Thanks!

    Naveen.
  • Jens Petersen
    Jens Petersen Member Posts: 4,730 Bronze Badge
    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562
  • 6363
    6363 Member Posts: 6,642
    SQL> select deptno,
    2 max(substr(sys_connect_by_path(ename,','),2)) emps
    3 from
    4 (
    5 select e.deptno, e.ename,
    6 row_number() over (
    7 partition by e.deptno order by e.ename
    8 ) curr,
    9 row_number() over (
    10 partition by e.deptno order by e.ename
    11 ) - 1 prev
    12 from emp e, dept d
    13 where e.deptno = d.deptno
    14 )
    15 start with curr = 1
    16 connect by prior curr = prev
    17 and prior deptno = deptno
    18 group by deptno
    19 /

    DEPTNO EMPS
    ------- ----------------------------------------
    10 CLARK,KING
    20 ADAMS,FORD,JONES,SCOTT,SMITH
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
    40 MILLER
  • Jens Petersen
    Jens Petersen Member Posts: 4,730 Bronze Badge
    3360,
    sys_connect_by_path does not work on 8i, it was introduced in 9i
  • naveenhks
    naveenhks Member Posts: 82
    Can any one help me out in this ............ Plzzzzz
  • Kamal Kishore
    Kamal Kishore Member Posts: 7,343
    Did you read the link posted above by Jens?
    Did you try the solution provided there?
  • naveenhks
    naveenhks Member Posts: 82
    Hi,
    I need the output as below :
    EMPS
    ----------------------------------------
    CLARK,KING,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD, MILLER

    The solution given Jens displays department number wise names , however i want all names to be displayed as mentione above irrespective of department number
  • 6363
    6363 Member Posts: 6,642
    Did you upgrade yet?
  • naveenhks
    naveenhks Member Posts: 82
    I am using oracle-8i, is there any possibility to get the result as specified using oracle 8i
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 6, 2010 11:34PM
    create table KeyList(
    Key char(3),
    Val char(2),
    primary key (Key,Val));
    insert into KeyList values('AAA','12');
    insert into KeyList values('AAA','2A');
    insert into KeyList values('AAA','32');
    insert into KeyList values('AAA','44');
    insert into KeyList values('BBB','11');
    insert into KeyList values('BBB','33');
    insert into KeyList values('BBB','8S');
    commit;
    col ConcatVal for a20
    select Key,
    substr(
    replace(
    replace(XMLAgg(XMLElement("dummy",Val) order by Val),'</dummy>'),
    '<dummy>',',')
    ,2) as ConcatVal
    from KeyList
    group by Key;
    select Key,max(SubStr(sys_connect_by_path(Val,','),2)) as ConcatVal
    from (select Key,Val,
    Lag(RowID) over(partition by Key order by Val) as LagRowID
    from KeyList)
    start with LagRowID is null
    connect by Prior RowID = LagRowID
    group by Key;
    OracleSQLPuzzle
    http://www.geocities.jp/oraclesqlpuzzle
  • Rengudi
    Rengudi Member Posts: 546
    Dear Naveenhks

    select 'empno '||empno||','||chr(15) from emp;

    Try this.

    Ranga
  • 495612
    495612 Member Posts: 1,052
    hi naveen, try this
    SQL> create table tst ( n varchar2(100);
    SQL> declare
    H varchar2(100):=' ' ;
    begin
    for R in (select ename from emp) loop
    H:=R.ENAME || ' , ' || H ;
    end loop;
    insert into tst values(H);
    end;
    /
    SQL> SELECT * FROM TST;
    MILLER,FORD,JAMES,ADAMS,TURNER,KING,SCOTT,CLARK,BLAKE,MARTIN,JONES,WARD,ALLEN,SMITH
    ----- is it what u need ?????
  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    edited Mar 9, 2006 7:40AM
    Is this what you are looking for?
    SQL> ;
    1 select max(substr(sys_connect_by_path(rtrim(ename), ','), 2, 200)) conc
    2 from (select ename, rownum rn
    3 from emp)
    4 start with rn = 1
    5* connect by rn = prior rn +1
    SQL> /

    CONC
    ----------------------------------------------------------------------------------------------------
    KING,BLAKE,CLARK,JONES,MARTIN,ALLEN,TURNER,JAMES,WARD,FORD,SMITH,SCOTT,ADAMS,MILLER

    SQL>
    Oh! I didn't see if it doesn't work on 8i.

    Cheers
    Sarma.

    Message was edited by:
    Radhakrishna Sarma
  • 495612
    495612 Member Posts: 1,052
    Naveen !!! Try this one also.
    SQL> set serveroutput on
    SQL>declare
    v varchar2(100):='';
    begin
    for r in (select ename from emp order by sal) loop
    v:=r.ename|| ',' ||v;
    end loop;
    dbms_output.put_line(v);
    end;
    /
    --------------
    KING,FORD,SCOTT,JONES,BLAKE,CLARK,ALLEN,TURNER,MILLER,MARTIN,WARD,ADAMS,JAMES,SMITH
  • 457512
    457512 Member Posts: 91
    Its Nice

    Thank you
    Raj Deep.A
  • I tried the fully generic string_agg function on Oracle 9.2, my output has a space between each character, could that be because my input is an NVARCHAR2?

    SQL> select string_agg(licensenumb)
    2 from clbce.avgrid
    3 group by addby;

    STRING_AGG(LICENSENUMB)
    --------------------------------------------------------------------------------

    a s d f a s f a s d g a s e r, k j h k
    A 1 2 3 A B C, 3 P R Y 0 8 6

    SQL> column plate format a50
    SQL> select string_agg(licensenumb) as plate
    2 from clbce.avgrid
    3 group by addby;

    PLATE
    --------------------------------------------------

    a s d f a s f a s d g a s e r, k j h k
    A 1 2 3 A B C, 3 P R Y 0 8 6

    SQL> describe clbce.avgrid
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    AVGRIDKEY NOT NULL NUMBER(9)
    CASEDETAILKEY NUMBER(9)
    ADDDTTM DATE
    ADDBY NVARCHAR2(30)
    MODBY NVARCHAR2(30)
    MODDTTM DATE
    MAKEOFCAR NVARCHAR2(10)
    MODELOFCAR NVARCHAR2(10)
    COMPLIANCEDATE DATE
    LICENSENUMB NVARCHAR2(15)
    VIN NVARCHAR2(25)

    SQL>

    7/5/2007
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    edited Jul 5, 2007 12:04PM
    Hi,
    Thanks for your response.

    I missed out to specify my database version. I am
    working on below specified version:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 -
    Production
    PL/SQL Release 8.1.7.4.0 - Production

    and moreover without using dept table is it possible
    to display all the employee names in single line with
    "," as delimiter.

    like a sql query which displays all the employee
    names present in employee table in a single string
    with "," as delimiter.

    Thanks!

    Naveen.
    Here it is
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.1
    - Production
    With the Partitioning option
    JServer Release 8.1.7.4.1 - Production

    SQL> set pagesize 9999;
    SQL>
    SQL> CREATE OR REPLACE
    2 type string_table as table of varchar2(4000)
    3 /

    Type created.

    SQL>
    SQL> CREATE OR REPLACE
    2 FUNCTION concat_strings (
    3 strtab IN string_table,
    4 sep IN varchar2
    5 )
    6 RETURN
    7 varchar2
    8 IS
    9 outval varchar2(4000);
    10 BEGIN
    11 outval := null;
    12 if (( strtab is not null )and (strtab.count > 0 ) ) then
    13 for i in strtab.first .. strtab.last loop
    14 if ( outval is null ) then
    15 outval := strtab(i);
    16 else
    17 if sep is not null then
    18 outval:=outval||sep||strtab(i);
    19 else
    20 outval:=outval||strtab(i);
    21 end if;
    22 end if;
    23 end loop;
    24 end if;
    25 return outval;
    26 END;
    27 /

    Function created.

    SQL> drop table KeyList
    2 /

    Table dropped.

    SQL> create table KeyList(
    2 Key char(3),
    3 Val char(2),
    4 primary key (Key,Val));

    Table created.

    SQL>
    SQL>
    SQL>
    SQL> insert into KeyList values('AAA','12');

    1 row created.

    SQL> insert into KeyList values('AAA','2A');

    1 row created.

    SQL> insert into KeyList values('AAA','32');

    1 row created.

    SQL> insert into KeyList values('AAA','44');

    1 row created.

    SQL> insert into KeyList values('BBB','11');

    1 row created.

    SQL> insert into KeyList values('BBB','33');

    1 row created.

    SQL> insert into KeyList values('BBB','8S');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT CONCAT_STRINGS( CAST (
    2 MULTISET (
    3 SELECT B.VAL
    4 FROM KEYLIST B
    5 WHERE B.key = A.KEY
    6 ORDER BY B.VAL
    7 ) AS STRING_TABLE
    8 ),',') as output
    9 FROM (
    10 SELECT DISTINCT KEY
    11 FROM KeyList
    12 ) A
    13 /

    OUTPUT
    ---------------

    12,2A,32,44
    11,33,8S


    SQL>
    Bye Alessandro
This discussion has been closed.