You need to have the same collation per column for each union statement.
Example
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable
WHERE condition
UNION ALL
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable2
WHERE condition
will work whereas the following won't
SELECT col1 collate Greek_CI_AS, col2 collate Latin1_General_CS_AS
FROM MyTable
WHERE condition
UNION ALL
SELECT col1 collate Latin1_General_CS_AS, col2 collate Greek_CI_AS
FROM MyTable2
WHERE condition