CI关系报表

CI关系报表

MSSQL：

SELECT ci.ciname          AS 'Name', 
       (SELECT rel.relationship + Char(10) + ' ' + ci2.ciname 
               + Char(10) FROM   cirelationships AS cirel 
               LEFT JOIN ci AS ci2 
                      ON ci2.ciid = cirel.ciid2 
               LEFT JOIN relationshiptype AS rel 
                      ON rel.relationshiptypeid = cirel.relationshiptypeid 
        WHERE  cirel.ciid = ci.ciid 
        FOR xml path('')) AS 'Relationship Details' FROM   ci 
WHERE  ciid IN (SELECT ciid 
                FROM   cirelationships) 

PGSQL ：

SELECT ci.ciname          AS 'Name', 
       (SELECT rel.relationship + Char(10) + ' ' + ci2.ciname 
               + Char(10) FROM   cirelationships AS cirel 
               LEFT JOIN ci AS ci2 
                      ON ci2.ciid = cirel.ciid2 
               LEFT JOIN relationshiptype AS rel 
                      ON rel.relationshiptypeid = cirel.relationshiptypeid 
        WHERE  cirel.ciid = ci.ciid 
        FOR xml path('')) AS 'Relationship Details' FROM   ci 
WHERE  ciid IN (SELECT ciid 
                FROM   cirelationships) 

    • Related Articles

    • SDP CI关系报表

      SDP CI关系报表 如指定配置项类型，参考如下SQL，红色标记内容为可替换配置项类型： SELECT ci.ciname AS "名称", (SELECT Array_to_string(Array_agg(Concat(rel.relationship, ' ', ci2.ciname)), ', ') FROM cirelationships AS cirel LEFT JOIN ci AS ci2 ON ci2.ciid = cirel.ciid2 LEFT JOIN ...