Its a pretty big query but here it is:

SELECT distinct

BASE1.AssetID AS a

,BASE1.Name As b

,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c

,replace(RELA.Name,char(10),'') As d

,BASE2.AssetID AS e

,BASE2.Name AS f

,BASE1.Company AS g

FROM

BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)

LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)

ON BASE1.InstanceId = RELA.Source_InstanceId

LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)

ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'

LEFT JOIN AST_CMDB_Associations AST (NOLOCK)

ON AST.request_id02 = BASE1.ReconciliationIdentity

and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'

and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1

and BASE1.AssetID = 'abcd'

UNION ALL

SELECT distinct

BASE1.AssetID AS a

,BASE1.Name As b

,case

when AST.form_name01 = 'HPD:Help Desk' then 'Incident'

when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'

when AST.form_name01 = 'PBM:Known Error' then 'Known Error'

when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'

when AST.form_name01 = 'RMS:Release' then 'Release'

when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c

,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d

,AST.Request_id01 AS e

,AST.Request_Description01 AS f

,BASE1.Company AS g

FROM

BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)

LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)

ON BASE1.InstanceId = RELA.Source_InstanceId

LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)

ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'

LEFT JOIN AST_CMDB_Associations AST (NOLOCK)

ON AST.request_id02 = BASE1.ReconciliationIdentity

and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'

and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1

and BASE1.AssetID = 'abcd'

This would give something like:

a b c d e f g

abcd SDPQ OPDA

abcd SDPQ Asset Record test dcba fgh OPDA