Dynamic SQL To Create View

I have a table that houses a list of store names that are performing under par. Each store name has it's own database on the server (we are working at merging all the databases, but at the current time all separate). Would it be possible to iterate the table (called failingstores) and create a view that holds the data for all failing stores? This is the table structure: (garbage data of course)

create table failingstores
(
  storename varchar(100)
  ,weeklysales int
  ,monthlysales int
  ,storemanager varchar(100)
)

Insert Into failingstores Values 
('one', 80, 800, 'managerone'), 
('two', 90, 900, 'managertwo'), 
('three', 40, 400, 'managerthree'), 
('four', 10, 100, 'managerfour')

The dynamic sql I would like to happen would be to iterate the values in the storename field and create a view. So this would be my view syntax:

Create view failingstoresfulldata As
Select * from managerone
union all
select * from managertwo
union all
select * from managerthree
union all
select * from managerfour

Solution

I had to do something similar recently and used dynamic SQL to pull the data from the databases into a local table. Storing into the local table is optional of course, but once it's there you can build whatever other SQL objects on top to query to your hearts content!

IF OBJECT_ID(N'tempdb..#Databases') is not null
    Drop Table #Databases

Select Name 
Into #Databases
From (Values
     ('Company1DB')
    ,('Company2DB')
    ,('Company3DB')
    ) as db(Name)

Declare @DBName varchar(50),@SQL nvarchar(max)=''

While (select count(*) From #Databases) >1 Begin
    Select Top 1 @DBName=Name From #Databases
    Select @DBName=isnull(@DBName,'')
    Set @SQL=@SQL+'Union All
    SELECT
        Column1
        ,Column2
        ,Column3
    FROM '+@DBName+'.tablename'

    Delete From #Databases where Name=@DBName
End 

Select @SQL=Stuff(@SQL,1,9,'') --- Remove leading "Union All"

Select @SQL --- Preview of Query

--- Pull the data into a single table for easy querying or further manipulation ---
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FullData') AND type in (N'U')) Begin
    Create Table FullData (
        Column1 int
        ,Column2 int
        ,Column3 int
    )
End Else Begin
    Truncate Table FullData
End

Insert Into FullData Exec SP_ExecuteSQL @SQL,N''

--- Cleanup ---
Drop Table #Databases