测试数据集的准备
IF (OBJECT_ID('item') IS NULL)
BEGIN
CREATE TABLE item (id int, [key] nvarchar(128), code nvarchar(128),
description nvarchar(512))
INSERT INTO item (id, [key] , code, description)
SELECT 1, 1, 'misc', 'miscellaneous' UNION ALL
SELECT 2, 1, 'med', 'medicine'UNION ALL
SELECT 3, 2, 'test not existing table', 'not_existing_table';
END
IF (OBJECT_ID('miscellaneous') IS NULL)
BEGIN
CREATE TABLEe miscellaneous (id int, code nvarchar(128), description
nvarchar(512))
INSERT INTO miscellaneous (id, code, description)
SELECT 1, 'misc1', 'miscellaneous' UNION ALL
SELECT 2, 'misc2', 'miscellaneous_xxx';
END
IF (OBJECT_ID('medicine') IS NULL)
BEGIN
CREATE TABLE medicine (id int, code nvarchar(128), description
nvarchar(512))
INSERT INTO medicine (id, code, description)
SELECT 1, 'medicine1', 'medicine' UNION ALL
SELECT 2, 'medicine2', 'medicine_xxx';
END
SQL脚本
DECLARE @joins nvarchar(max) ='',@sql NVARCHAR(MAX) = ' SELECT * FROM item';
SELECT @joins = STUFF((SELECT ( CHAR(13)+CHAR(10) + ' LEFT JOIN ' +
description + ' ' + description + ' ON ' + description +'.id = item.[key] AND item.description = ''' + description + '''' )
FROM item i WHERE ISNULL(description,'') != '' AND
(select OBJECT_ID(description) ) IS NOT NULL
GROUP BY description
ORDER BY description
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'');
IF(ISNULL(@joins,'') != '' )
BEGIN
SET @sql = @sql + @joins;
EXECUTE sp_executesql @sql;
PRINT @sql
END;