showing rows as columns

[syncdba]

Hi,

I have table as:

Id Name
1 Apps
1 Browser_rd
1 Multimedia
2 Browser_sd
2 Multimedia
2 Voice
3 Apps
3 Voice

I need to create a sql which shows Rows of "Name" as its columns group by ID Like:

1 Name Apps Browser_rd Multimedia
2 Name Browser_sd Multimedia Voice
3 Name Apps Voice

The number of rows varies by ID column.

Thanks in Advance.

 

[SantaMufasa]

You are welcome to use my MATRIX function:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||','||incoming;
    end loop;
    return ltrim(hold_result,',');
END;
/

Function created.

col text format a30
select id
      ,replace
       (matrix
        ('select name from syncdba where id = '||id
        ),',',' '
       )
       text
  from syncdba
 group by id;

 ID TEXT
--- ---------------------------
  1 Apps Browser_rd Multimedia
  2 Browser_sd Multimedia Voice
  3 Apps Voice

3 rows selected.

Let us know if this satisfactorily resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

see thread1177-1345052

 

[syncdba]

Thanks SantaMufasa. Its working. The query shows all the rows in one column for particular group but can we do single row as single column without concatenating. Like:

ID TEXT TEXT TEXT
--- ---------- ---------- -----------
1 Apps Browser_rd Multimedia
2 Browser_sd Multimedia Voice
3 Apps Voice

Thanks for your help.

 

[SantaMufasa]

The simplest method to achieve that result would be a user-defined function (I'll call "GET_VAL" for the sake of discussion). It would be similar to my earlier concatenation-style code, but instead, it would return the "n-th" item of an unlimited number of items.

The invocation would look something like this:

SELECT ID
      ,GET_VAL(ID,1) text
      ,GET_VAL(ID,2) text
      ,GET_VAL(ID,3) text
from syncdba
group by ID;

I would build and post a copy of "GET_VAL", but (as I mentioned in another thread) I'm just trying to get to my daughter's wedding reception. If you have not received/derived a solution by the time I check next, I'll post a copy then.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Syncdba,

Here is the code that does what you requested:

Section 1 -- Sample data:

select * from syncdba;

   ID NAME
----- ----------------
    1 Apps
    1 Browser_rd
    1 Multimedia
    2 Browser_sd
    2 Multimedia
    2 Voice
    3 Apps
    3 Voice

8 rows selected.

Section 2 -- "GET_VAL" function definition:

create or replace function get_val
                  (id_in number, which_item number)
                   return varchar2 is
begin
    for i in (select rownum rn, x.* from (select *
                               from syncdba
                              where id = id_in
                              order by name) x
             ) loop
        if i.rn = which_item then
            return i.name;
        end if;
    end loop;
    return null;
end;
/

Function created.

Section 3 -- Sample Invocation of "GET_VAL" function. (Note that I requested four iterations of values for each row to show that NULL appears when data does not exist for a particular ordinal request.):

col text format a10
SELECT ID
      ,GET_VAL(ID,1) text
      ,GET_VAL(ID,2) text
      ,GET_VAL(ID,3) text
      ,GET_VAL(ID,4) text
from syncdba
group by ID;

   ID TEXT       TEXT       TEXT       TEXT
----- ---------- ---------- ---------- ----------
    1 Apps       Browser_rd Multimedia
    2 Browser_sd Multimedia Voice
    3 Apps       Voice

3 rows selected.

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]