[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

Group records where linked field is the same (Excel/SQL)

I have two tables Controls & Systems, which are left outer joined (controls - systems).  

A control can have multiple systems attached to it.  Currently the results come back as :

Control No   Control    System                        
01254          aaaaa     excel
01254          aaaaa     oracle
01252          bbbbb     oracle
02543          ccccc    
01275          ddddd     excel
01275          ddddd     outlook
01275          ddddd     word
However I want it to come back as:

Control No   Control    System
01254          aaaaa     excel | oracle
01252          bbbbb     oracle
02543          ccccc    
01275          ddddd     excel | outlook | word

When I originally prepared the reports in Crystal it did come back like this.  However when I try it now in Excel or Crystal it seperates them out into indvidual rows.

Is there any way of combining the Systems results onto one row?  Any help would be appreciated
0
ITmonkeys
Asked:
ITmonkeys
  • 2
2 Solutions
 
NavicertsCommented:
So Controls is keyed on [Control No] while Systems is keyed on [Control No] AND [System]?
0
 
NavicertsCommented:
create function dbo.concate_System ( @ControlNo int )
returns varchar(8000)
as
begin
  declare @res varchar(8000)
  select @res = coalesce( @res + ',' , '') + [System]
  from [Systems] where [Control No]= @ControlNo
  return (@res)
end

GO

select [Control No], dbo.concate_System([Control No])
from [Systems]
group by [Control No]
0
 
YANN0SCommented:
You can create a PL/SQL function to return multiple systems as a single pipe separated string:
Assuming that you have two tables
controls (control_no, control_name)
and
systems(control_no, system_name)

CREATE OR REPLACE FUNCTION get_systems(p_control_no IN controls.control_no%TYPE)
RETURN VARCHAR2
AS
ret_val VARCHAR2(4000);
BEGIN
FOR i IN
(SELECT system_name
    FROM systems
  WHERE control_no = p_control_no
 ORDER BY  system_name)
LOOP
       ret_val := ret_val ||i.system_name||' | ';
END LOOP;
ret_val := RTRIM (ret_val , ' | ');
RETURN ret_val;
END;
/

Then your statement should be

SELECT control_no, control_name, get_systems(control_no)
FROM controls;
0
 
ITmonkeysAuthor Commented:
Hi,

Apologies neither of these solutions worked.  I'm trying an alternative way so I'll close this Question and split the points
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now