Home » SQL & PL/SQL » SQL & PL/SQL » Combining common rows and concatonating their ID
Combining common rows and concatonating their ID [message #195815] Mon, 02 October 2006 10:28 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,

I have the following SQL
Select v.PROJECTNO, 
p.unique_name


FROM
niku.srm_projects p,
niku.dsti_pds_info_v v

WHERE
v.PROJECTID = p.unique_name


this will return multiple records with the same PROJECTNO, but with different unique_names.
I need to be able to show only one row for each PROJECTNO, and in the other column, show a comma seperated list of the UNIQUE_NAMES associated with them.

can anybody help me with this please?
thanks in advance,
Matt
Re: Combining common rows and concatonating their ID [message #195825 is a reply to message #195815] Mon, 02 October 2006 11:54 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You haven't really specified what your data model looks like, but from your query I would infer dsti_pds_info_v contains:

Proj_no, Proj_id
1,       'History'
1,       'Geography'
2,       'Physics'

and srm_projects just contains

UniqueName
'History'
'Geography'
'Physics'

Srm_projects doesn't seem to be adding anything in this instance, so I'll skip it in the example I give.

There is no general way to convert an arbitrary number of rows into columns, but the are some approaches you could take.

If the number of course names is finite (up to a maximum of 3 say), you could do something like:

SELECT projectno,
rtrim(max(case when rn = 1 then projectid else null end)||','||
max(case when rn = 2 then projectid else null end)||','||
max(case when rn = 3 then projectid else null end),',')
from
    (Select PROJECTNO, 
            PROJECTID,
            row_number() over 
             (partition by projectno 
              order by projectid) as rn 
FROM
    niku.dsti_pds_info_v v)
group by projectno


Otherwise, you could write a PL/SQL function which returns the list of project names for a given projectno. Something like:

create or replace function 
f_get_name_list(p_project_no in niku.dsti_pds_info_v.PROJECTNO%type) return string is
   v_output varchar2(10000);
begin
   for rec in (select projectid from   
                niku.dsti_pds_info_v
               where projectno = p_project_no) loop
      v_output := v_output||rec.projectid||',';
   end loop;
   return rtrim(v_output, ',');
end;
/

select project_no, f_get_name_list(project_no)
from 
(select distinct project_no from niku.dsti_pds_info_v)
Re: Combining common rows and concatonating their ID [message #195927 is a reply to message #195825] Tue, 03 October 2006 03:22 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi Cthulhu,

thats great.
I tried both options.
The function takes some time to execute when I use that in the SQL - so I think i'll go for the first option.
I may need to go down to another level of granularity with it though.
at most there will be 4 references for each project.
2 design, signified by an 'S' in the 7th character of the reference, and 2 development signified by a 'D' in the 7th character.

Ultimately, what I need to be able to achieve is a single row for the project with the Design figures followed by the development figures.

e.g.

Project DesRefs DesActs DevRefs DevActs
UK123 Ref1, Ref2 40 Ref3, Ref4 35

I was thinking about using your first option to create a view that contains all design refererences, then a second view that contains all development references.

I can then link these into our project tables to return other project level data.

What I need to be able to do though is SUM the actuals for each ref.

Lets say, hypothetically that there was a third column in my original post with actuals. How would you sum them up?

many thanks for your help,
Matt
Re: Combining common rows and concatonating their ID [message #195930 is a reply to message #195815] Tue, 03 October 2006 03:33 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Expanding the query I gave before, you could do:

SELECT projectno,
rtrim(max(case when rn = 1 then projectid else null end)||','||
max(case when rn = 2 then projectid else null end)||','||
max(case when rn = 3 then projectid else null end),','),
sum(actual_val)
from
    (Select PROJECTNO, 
            PROJECTID,
            ACTUAL_VAL,
            row_number() over 
             (partition by projectno 
              order by projectid) as rn
FROM
    niku.dsti_pds_info_v v)
group by projectno


You can also get a sum as if it were an additional column using partitioning functions:

Select PROJECTNO, 
       PROJECTID,
       sum(actual_val) over 
          (partition by projectno) as sum_act_val
FROM
    niku.dsti_pds_info_v
Previous Topic: How to find the number of saturdays and Sundays?
Next Topic: Regarding VARRAY ...
Goto Forum:
  


Current Time: Sun Dec 11 00:04:20 CST 2016

Total time taken to generate the page: 0.08293 seconds