Author Topic: SQL Query that reports 1..* related elements as comma separated list on one row  (Read 1114 times)

jvdens

  • EA Novice
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hi,  I'm struggling with a SQL query for reporting on a software catalogue based on the following model elements in EA:
- we use components  to represent software tools (stereotype "tool"),
- each tool can have one vendor associated to it (component with stereotype "company"),
- each tool can have many capabilities associated to it (activities with stereotype "capability").

I try to create a SQL query that has this desired result:

name  | notes                     | vendor   | capabilities
----------------------------------------------------------
tool 1 | description of tool 1 | vendor x | cap1, cap2, cap3
tool 2 | description of tool 2 | vendor y | cap2, cap4

The hard part is: the comma separated list of capabilities.
I'm halfway: I created a query that results in the correct collums, but has too many row (each for one of related capabiliies):

name  | notes                     | vendor    | capabilities
-----------------------------------------------------------
tool 1 | description of tool 1 | vendor x | cap1
tool 1 | description of tool 1 | vendor x | cap2
tool 1 | description of tool 1 | vendor x | cap3
tool 2 | description of tool 2 | vendor y | cap2
tool 2 | description of tool 2 | vendor y | cap4

We use a SQL Server DB, (and EA 15.2).

Here's the "halfway" query - I know the answer probably relies on SQL functions like STRING_AGG, but I can't find how to apply..
----------------------------------------------------------------------------------------------------

SELECT t_object.ea_guid AS CLASSGUID, t_object.Name , t_object.Note , VendorName , Capability
from t_object 
-- look for associated vendor
left join (select vendorconnector.Start_Object_ID, thevendor.Name as VendorName
            from t_connector vendorconnector
            inner join t_object thevendor on (  thevendor.Object_ID = vendorconnector.End_Object_ID )
            where (thevendor.Stereotype = 'company'))
         AS vendorjoin
         ON vendorjoin.Start_Object_ID = t_object.Object_ID -- left join with answer
-- look for associated capabilities
left join (select capabilityconnector.Start_Object_ID, theCapability.Name as Capability
            from t_connector capabilityconnector
            inner join t_object theCapability on (  theCapability.Object_ID = capabilityconnector.End_Object_ID )
            where (theCapability.Stereotype = 'capability'))
         AS capjoin
         ON capjoin.Start_Object_ID = t_object.Object_ID -- left join with answer2         
-- basic conditions for the resulting t_objects
where (  (  t_object.Stereotype = 'tool' ) and  (t_object.Object_type = 'Component')  )         

ORDER BY t_object.Name
------------------------------------------------------------------------------------------------------
Any help would be greatly appreciated!!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
I use a combination of REPLACE,  STUFF and FOR XML PATH do do the same. This was the only technique you could use before STRING_AGG was available.
I do believe you can get the same result (but easier) with STRING_AGG, but I haven't gotten around to try that yet.


Code: [Select]
select rq.Name as Requirement,
Case
when rq.stereotype = 'Compatibilit' then 'Integriteit'
when rq.stereotype = 'Reliability' then 'Beschikbaarheid'
when rq.stereotype = 'Functional' then 'Privacy'
when rq.stereotype = 'Security' then 'Vertrouwelijkheid'
else rq.Stereotype
end as RQType
, rq.Note as [Description-Formatted]
,'todo' as Systems
 
 ,Replace((
  select STUFF((
select distinct ',' + s.Name
from  t_connector cs
inner join t_object s on s.Object_ID = cs.End_Object_ID
and s.Stereotype = 'EAM_ApplicationComponent'
where cs.Start_Object_ID = rq.Object_ID
and cs.Stereotype = 'trace'
  FOR XML PATH ('')
),1,1,'')
), ',', char(13) + char(10) ) as [Systems-Formatted]
 from t_object o
inner join t_connector c on c.End_Object_ID = o.Object_ID
and c.Stereotype = 'trace'
inner join t_object rq on rq.Object_ID = c.Start_Object_ID
and rq.Object_Type = 'Requirement'
where o.Object_ID = #OBJECTID#

Geert

jvdens

  • EA Novice
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hi Geert,

thank you VERY much for this fine piece of SQL magic!!!
I applied it to my "half way" query.. and it works! :D :D :D  and I probably will be using this pattern a lot in many other use cases.

grtz,  Joris

here's the result:
Code: [Select]
SELECT t_object.ea_guid AS CLASSGUID, t_object.Name, t_object.Note,
Replace((
  select STUFF((
select distinct ',' + s.Name
from  t_connector cs
inner join t_object s on s.Object_ID = cs.End_Object_ID and s.Stereotype = 'capability'
where cs.Start_Object_ID = t_object.Object_ID
  FOR XML PATH ('')
),1,1,'')
), ',', char(13) + char(10) ) as [Capabilities],
VendorName
from t_object 
-- look for associated vendor
left join (select vendorconnector.Start_Object_ID, thevendor.Name as VendorName
from t_connector vendorconnector
inner join t_object thevendor on (  thevendor.Object_ID = vendorconnector.End_Object_ID )
where (thevendor.Stereotype = 'company'))
AS vendorjoin
ON vendorjoin.Start_Object_ID = t_object.Object_ID -- left join with answer
-- basic conditions for the resulting t_objects
where (  (  t_object.Stereotype = 'tool' ) and  (t_object.Object_type = 'Component')  )
ORDER BY t_object.name

Richard Freggi

  • EA User
  • **
  • Posts: 390
  • Karma: +14/-7
    • View Profile
No time to read in detail, but is this not a job for STRING_AGG?  Your could STRING_AGG(this, ' | ') || STRING_AGG(that, ', ')

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
No time to read in detail, but is this not a job for STRING_AGG?  Your could STRING_AGG(this, ' | ') || STRING_AGG(that, ', ')
Yes, we mentioned that. My code is from before STRING_AGG existed, so this was the workaround back then.
Would be interesting to see how the query looks with STRING_AGG

Geert