I know this sounds weird, but is it possible to have a view that use dynamic SQL to build it? I know the views are compiled so most probably this is not possible. For sure I could do it using an stored procedure instead but I just want to make sure is not possible.
Here I have an example:
declare @Table1 as table (
Id int,
Name nvarchar(50),
Provider nvarchar(50)
)
insert @Table1 values (1, 'John', 'Provider1')
insert @Table1 values (2, 'Peter', 'Provider1')
insert @Table1 values (3, 'Marcus', 'Provider2')
declare @Table2 as table (
Id int,
Info nvarchar(50),
AnotherInfo nvarchar(50)
)
insert @Table2 values (1, 'Expense', '480140')
insert @Table2 values (1, 'Maintenance', '480130')
insert @Table2 values (2, 'Set Up Cost', '480150')
insert @Table2 values (2, 'Something', '480160')
--No columns from Table2
select a.Id, a.Name, a.Provider from @Table1 a left join @Table2 b on a.Id = b.Id
--With columns from Table2
select a.Id, a.Name, a.Provider, b.Info, b.AnotherInfo from @Table1 a left join @Table2 b on a.Id = b.Id
The first select looks like I have repeated data, which is normal because I did the left join, the problem is that for avoiding that I need to perform a distinct and this is what I don't want to do. My example is short but I have much more columns and table is quite big.