MSSQL :
SELECT MAX(resource.RESOURCENAME) AS "Asset Name", MAX(productType.COMPONENTTYPENAME) AS "Product Type", MAX(product.COMPONENTNAME) AS "Product", MAX(resource.ASSETTAG) AS "Asset Tag", MAX(resource.SERIALNO) AS "Org Serial Number", STRING_AGG(sda.ATTACHMENTNAME, ', ') AS "Attachments" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID = product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID = productType.COMPONENTTYPEID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID = state.RESOURCESTATEID LEFT JOIN ResourceAttachment ra On resource.RESOURCEID = ra.RESOURCEID LEFT JOIN Sdeskattachment sda ON ra.ATTACHMENTID = sda.ATTACHMENTID WHERE state.DISPLAYSTATE = 'Disposed' and sda.ATTACHMENTNAME<>'未指派' GROUP BY resource.resourcename
PGSQL:
SELECT MAX(resource.RESOURCENAME) AS "Asset Name", MAX(productType.COMPONENTTYPENAME) AS "Product Type", MAX(product.COMPONENTNAME) AS "Product", MAX(resource.ASSETTAG) AS "Asset Tag", MAX(resource.SERIALNO) AS "Org Serial Number", STRING_AGG(sda.ATTACHMENTNAME, ', ') AS "Attachments" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID = product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID = productType.COMPONENTTYPEID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID = state.RESOURCESTATEID LEFT JOIN ResourceAttachment ra On resource.RESOURCEID = ra.RESOURCEID LEFT JOIN Sdeskattachment sda ON ra.ATTACHMENTID = sda.ATTACHMENTID WHERE state.DISPLAYSTATE = 'Disposed' and sda.ATTACHMENTNAME<>'未指派' GROUP BY resource.resourcename