T-SQL循环通过基于多个视图的映射表

Question

我们有多个视图，需要从中获取值。SQL中有一个表，其中映射了视图和列名。

映射表：

| Id | ViewName | ViewColumnName   |
| ---+----------+------------------|
| 1  | vwCars   | MotorType        |
| 2  | vwCars   | FourWheelDrive   |
| 3  | vwCars   | TransmissionType |
| 4  | vwBikes  | MotorType        |
| 6  | vwBikes  | TransmissionType |
| 7  | vwQuads  | MotorType        |
| 9  | vwQuads  | FourWheelDrive   |
| 16 | vwQuads  | TransmissionType |

我创建了一个T-SQL查询，它可以遍历像这样的每个组合，并获得所需的值：

select distinct MotorType from vwCars where MotorType is not null
union
select distinct FourWheelDrive from vwCars where FourWheelDrive is not null
union
...

这是一个查询：

declare @sql nvarchar(max) = '';
declare @i int = 0;
declare @j int = (select max(Id) from MappingTable);
declare @ViewName nvarchar(80);
declare @ViewColumnName nvarchar(80);
while(@i < @j + 1)
begin
set @ViewName = (select ValueView from MappingTable where Id = @i);
set @viewColumnName = (select ValueColumn from MappingTable where Id = @i);
if (select 1 from MappingTable where Id = @i) = 1
set @sql += 'select distinct cast(' + @ViewColumnName + ' as nvarchar(80)) as Bezeichnung from ' + @ViewName + ' where ' + @ViewColumnName + ' IS NOT NULL
';
if (select 1 from MappingTable where Id = @i) = 1 and (@i < @j) set @sql += 'union
';
set @i = @i + 1;
end;
print(@sql);

最大的问题是，当Id中有空格时，也要完成所有操作。Query可以工作，但我想知道是否有更好的方法。

Answer 1

不需要while循环。试试这样的东西：

DECLARE @s varchar(max) = ''
SELECT  @s = @s + 'select distinct ' + ViewColumnName + ' from ' + viewname + ' where ' + ViewColumnName + ' is not null
UNION '
FROM    MappingTable
SET @s = LEFT(@s, LEN(@s)-8)
print @s