Create List of Diagrams an Object is in

[Curtis]

Hello,

I've been trying to create a SQL query to create a comma separated list of where each object appears in a diagram. Here's what I have so far:
---------
SELECT t_object.Object_ID, t_object.Name, t_diagram.Name

FROM (t_diagramobjects
LEFT JOIN t_object ON t_diagramobjects.Object_ID = t_object.Object_ID)
LEFT JOIN  t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID

ORDER BY t_object.Object_ID
-----------
I tried using examples such as http://stackoverflow.com/questions/12668528/sql-server-group-by-clause-to-get-comma-separated-values to no avail.

Any help would be appreciated. Thanks!

[smendonc]

The below should do what I think you are looking for.  The string concatenation ('+') and case statement are specific to SQL Server.

• Objects with no names that exist on diagrams represent diagram specific objects like boundaries that may not have been named.

----------
select  cast(o.Object_ID as nvarchar)
         + cast(',' as nvarchar)
         + case when o.Name is null then '' else o.Name end
       + cast(',' as nvarchar)
       + case when d.Name is null then '' else d.Name end 'Object_ID,ObjectName,DiagramName'
  from t_object o
       left join t_diagramobjects do on (do.Object_ID = o.Object_ID)
         left join t_diagram d on (do.Diagram_ID = d.Diagram_ID)
 order by o.Object_ID