Yes, you can create a view that looks at data in another database. e.g.
You have database1 and database2. database1 contains table1 and database2 contains tables2
By using 3 part naming syntax to qualify your database objects, we can create a view in database1 that looks like the following
The syntax here is [database].[owner].[object]
SELECT
Field1, Field2, Field3
FROM
database1.dbo.table1 T1
INNER JOIN
database2.dbo.table2 T2
WHERE
T1.SomeField = T2.SomeField
You might have security issues with anyone consuming this view. They would need permissions to both the databases and objects.
EDIT: sorry, you updated you answer while I wrote mine!
You can use a
4 part naming query[
^] for this.
SELECT
Field1, Field2, Field3
FROM
localhost.database1.dbo.table1 T1
INNER JOIN
REMOTESERVER.database2.dbo.table2 T2
WHERE
T1.SomeField = T2.SomeField
However, you'll definitely run into security issues with this and more than likely some performance issues
Maybe investigate using
OPENQUERY[
^] to grab the data you need from your REMOTESERVER and perform aggregation locally.
Totally depends on how much data you're working with