Multiple Selects With One Column Encrypted
I don't believe the issue is concerning the encryption. You are doing two sub-queries either of which could contain multiple rows, hence the error. If both source tables are the same, you can re-write as: -

SELECT COLUMNA AS 'NumberA',
CAST(aes_decrypt(COLUMNB,'key','IV') AS CHAR) AS 'Number2'
FROM TABLE