Home » SQL & PL/SQL » SQL & PL/SQL » columns to row (ORACLE 10.2.0.3.0)
columns to row [message #321374] Tue, 20 May 2008 01:54 Go to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
try to CONCAT some rows as a result of a SELECT together in one field, as shown below:

with a as
(
select 1 as id, 'Albert' as fnam from dual
union all
select 3      , 'Harry'          from dual
union all
select 2      , 'Ida'            from dual
union all
select 1      , 'Ludwig'         from dual
union all
select 3      , 'Theo'           from dual
)
select id, collect(fnam) as nstr from a group by id


This works fine.

Is it possible to "UNNEST" the result, that I could get 2 columns like:

1 'Albert Ludwig'
2 'Ida'
3 'Harry Theo'

Thx alot
Re: columns to row [message #321379 is a reply to message #321374] Tue, 20 May 2008 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, search for "pivot", this is asked several times each week.

Regards
Michel
Re: columns to row [message #321385 is a reply to message #321374] Tue, 20 May 2008 02:31 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Thanks for Your reply, I hoped to find another (SQL) solution that could CAST the NESTED TABLE in each row direct in a STRING.
Re: columns to row [message #321387 is a reply to message #321385] Tue, 20 May 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways, you can find if you type "pivot" in the search field.

Regards
Michel
Re: columns to row [message #321526 is a reply to message #321374] Tue, 20 May 2008 10:32 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
got it at least, here the details for some other guys with the same problem:
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

with a as
(
select 1 as id, 'Albert' as fnam from dual
union all
select 3      , 'Harry'          from dual
union all
select 2      , 'Ida'            from dual
union all
select 1      , 'Ludwig'         from dual
union all
select 3      , 'Theo'           from dual
)
select id, tab_to_string(cast (collect(fnam) t_varchar2_tab)) as nstr from a group by id
Previous Topic: database link
Next Topic: Problem with FORALL
Goto Forum:
  


Current Time: Mon Dec 05 02:42:03 CST 2016

Total time taken to generate the page: 0.08833 seconds