SQL - returning a list of id for every row in the database

Question

Hi All,

I have the following query

DECLARE      @tagIDs VARCHAR(1000)          
DECLARE      @TagValues VARCHAR(8000)  

SELECT       @tagIDs = ISNULL(@tagIDs + ',', '') + CAST(CTTagLookup.tagID AS VARCHAR(1000)), @TagValues = ISNULL(@TagValues + ',', '') + CAST(CTTag.tag AS VARCHAR(8000))  
FROM         CTTagLookup INNER JOIN CTTag ON CTTag.tagId = CTTagLookup.tagId  
WHERE    CTTagLookup.assetId = 122  

SELECT DISTINCT CTAsset.assetID, CTAsset.isLive, @tagIDs AS tagID  
FROM CTAsset  

This works great When I add a assetId (in this case 122) but I want it to work for each row in the CTAsset table. Can this be done?

Thanks in advance