Meta Query将查询结果转换为多个表和列的动态连接

Question

我想将查询的结果转换为具有多个JOIN和列的动态查询。每个表还有一个名为"DT"的公共列。这是一行的datetime。关于负责与列DT进行JOIN(这里是t1.DT)的表，它可以是它们中的任何一个(因此它可以与t2一起工作)。Dt或t3, Dt)我才不在乎桌子的顺序呢

初始矩阵(表，列，描述)是我之前执行的查询的结果。

Table   Column  Description
T5165   C25086  01 - Wind direction
T5165   C25182  01 - Nacelle position - degrees
T5165   C25472  02 - Wind direction
T5165   C25568  02 - Nacelle position - degrees
T5165   C25858  03 - Wind direction
T5165   C25954  03 - Nacelle position - degrees
T5165   C26244  04 - Wind direction
T5165   C26340  04 - Nacelle position - degrees
T5165   C26630  05 - Wind direction
T5165   C26726  05 - Nacelle position - degrees
T5165   C27016  06 - Wind direction
T5165   C27112  06 - Nacelle position - degrees
T550    c25250  01 - Unfiltered generator speed
T551    c25636  02 - Unfiltered generator speed
T552    c26022  03 - Unfiltered generator speed
T553    c26408  04 - Unfiltered generator speed
T554    c26794  05 - Unfiltered generator speed
T555    c27180  06 - Unfiltered generator speed

让我给你们看一个前3个表的例子。这只是我想要的结果的开始但是我想要自动生成。

SELECT
TOP 100
CASE WHEN t1.DT IS NULL THEN 
CASE WHEN t2.dt IS NULL
THEN t3.dt
ELSE 
t2.dt
END
ELSE 
T1.DT 
END DT,
t1.C25086   [01 - Wind direction],
t1.C25182   [01 - Nacelle position - degrees],
t1.C25472   [02 - Wind direction],
t1.C25568   [02 - Nacelle position - degrees],
t1.C25858   [03 - Wind direction],
t1.C25954   [03 - Nacelle position - degrees],
t1.C26244   [04 - Wind direction],
t1.C26340   [04 - Nacelle position - degrees],
t1.C26630   [05 - Wind direction],
t1.C26726   [05 - Nacelle position - degrees],
t1.C27016   [06 - Wind direction],
t1.C27112   [06 - Nacelle position - degrees],
t2.c25250   [01 - Unfiltered generator speed],
t3.c25636   [02 - Unfiltered generator speed]
FROM
T5165 t1
FULL JOIN
T550 t2 ON t2.DT = t1.DT
FULL JOIN
T551 t3 ON t3.DT = t1.DT

所以困难在于表和列的数量是可变的，必须在运行时确定。我不想硬编码结果，因为在最终结果中有很多表和列。我只是发布了矩阵的一小部分。

Answer 1

我很晚了，你是基于相同的日期加入的，这只会得到日期不为空的记录，所以不需要使用CASE来检查空，所以我只使用第一个表的日期。

你加入所有的全连接，我发现更简单的只是添加表与逗号，然后应用到所有的地方。

--##################################
--######## SOURCE:
CREATE TABLE Temp
(
[Table]   VARCHAR(100),
[Column]  VARCHAR(100),
[Description] VARCHAR(100)
)
INSERT INTO Temp VALUES ('T5165',    'C25086',   '01 - Wind direction'),('T5165',    'C25182',   '01 - Nacelle position - degrees'),('T5165',    'C25472',   '02 - Wind direction'),('T5165',    'C25568',   '02 - Nacelle position - degrees'),('T5165',    'C25858',   '03 - Wind direction'),('T5165',    'C25954',   '03 - Nacelle position - degrees'),('T5165',    'C26244',   '04 - Wind direction'),('T5165',    'C26340',   '04 - Nacelle position - degrees'),('T5165',    'C26630',   '05 - Wind direction'),('T5165',    'C26726',   '05 - Nacelle position - degrees'),('T5165',    'C27016',   '06 - Wind direction'),('T5165',    'C27112',   '06 - Nacelle position - degrees'),('T550',     'c25250',   '01 - Unfiltered generator speed'),('T551',     'c25636',   '02 - Unfiltered generator speed'),('T552',     'c26022',   '03 - Unfiltered generator speed'),('T553',     'c26408',   '04 - Unfiltered generator speed'),('T554',     'c26794',   '05 - Unfiltered generator speed'),('T555',     'c27180',   '06 - Unfiltered generator speed')
--##################################
--######## DYNAMIC QUERY:
DECLARE @dateParameter VARCHAR(100) = '2022-01-01'
DECLARE @dateColumn AS NVARCHAR(MAX) = (SELECT TOP 1 temp.[Table] + '.DT,' FROM temp)
DECLARE @selectColumns AS NVARCHAR(MAX) = ' SELECT ' + @dateColumn + STUFF((SELECT distinct ',' + temp.[Table] + '.' + temp.[Column] + ' ' + QUOTENAME(temp.[Description]) 
FROM temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
DECLARE @fromTables AS NVARCHAR(MAX) = ' FROM ' + STUFF((SELECT distinct ',' + temp.[Table]
FROM temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
DECLARE @whereDateEquals AS NVARCHAR(MAX) = ' WHERE ' + STUFF((SELECT distinct ' AND ' + temp.[Table] + '.DT = ''' + @dateParameter + ''''
FROM temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,5,'')
EXEC(@selectColumns + @fromTables + @whereDateEquals)

如果你还需要这个，告诉我如果它工作，如果你不需要了，但发现我的答案有用，如果你可以标记我的回答为接受，我将感激= d

Answer 2

谢谢你的回答，我做了一些你希望使用动态sql。因此，结果可能是更脏，但工作完成了。

CREATE PROCEDURE dbo.get_wind_farm_reporting(
@IdParc INT,
@Debut  DATETIME,
@FIN    DATETIME)
AS
BEGIN
DECLARE @sqlSelect VARCHAR(MAX)
DECLARE @sqlFrom VARCHAR(MAX)
DECLARE @sqlWhere VARCHAR(MAX)
DECLARE @sqlResult VARCHAR(MAX)
DECLARE @sqlOrder VARCHAR(MAX)
DECLARE @strTable VARCHAR(100)
DECLARE @strColumn VARCHAR(100)
DECLARE @strDescrip VARCHAR(100)
DECLARE @intTable INT
DECLARE @intColumn INT
DECLARE @nbTable INT
DECLARE @nbColumn INT
DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @IsFirstTable BIT = 1
DECLARE @IsLastColumn BIT = 0
IF OBJECT_ID('tempdb..#selectedFarm') IS NOT NULL DROP TABLE #selectedFarm
IF OBJECT_ID('tempdb..#unikTable') IS NOT NULL DROP TABLE #unikTable
IF OBJECT_ID('tempdb..#unikColumn') IS NOT NULL DROP TABLE #unikColumn
IF OBJECT_ID('tempdb..#farmTable') IS NOT NULL DROP TABLE #farmTable

SELECT  
[Txxx (Table)] IdTable, 
[dbo].get_kerwin_column_name([Txxx (Table)], [Cxxx_1 (Colonne)]) columnName,       
[Voie Libelle] Descrip 
INTO #selectedFarm
FROM 
[KERWIN_SQL].[dbo].[get_table_columns_wind_data] ks
WHERE
ks.IdParc = @IdParc
SELECT DISTINCT IdTable into #farmTable FROM #selectedFarm
SELECT DISTINCT IdTable, ROW_NUMBER() OVER(ORDER BY IdTable ASC) AS IdRow into #unikTable FROM #farmTable
SELECT @nbTable = count(0) FROM #unikTable
WHILE @i <= @nbTable
BEGIN
SELECT @intTable = IdTable FROM #unikTable WHERE IdRow = @i
IF OBJECT_ID('tempdb..#unikColumn') IS NOT NULL DROP TABLE #unikColumn
SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName ASC) AS IdRow into #unikColumn FROM #selectedFarm WHERE IdTable = @intTable
SELECT @nbColumn = count(0) FROM #unikColumn
SET @j = 1
print 'Table ' + CONVERT(VARCHAR(100), @intTable)
WHILE @j <= @nbColumn
BEGIN
SELECT @strColumn = columnName FROM #unikColumn WHERE IdRow = @j
SELECT @strDescrip = Descrip FROM #selectedFarm WHERE columnName = @strColumn and IdTable = @intTable
SET @sqlSelect = CASE @IsFirstTable
WHEN 1 THEN 
'SELECT
t1.DT,'  + ' t' + CONVERT(VARCHAR(100), @i) + '.' + @strColumn + ' [' + @strDescrip + ']'
ELSE
@sqlSelect + ' t' + CONVERT(VARCHAR(100), @i) +'.'+ @strColumn + ' [' + @strDescrip + ']'
END         
IF @i <> @nbTable OR @j <> @nbColumn SET @sqlSelect = @sqlSelect + ','
SET @j = @j + 1
END
SET @sqlFrom = CASE @IsFirstTable WHEN 1 THEN
' FROM T' + CONVERT(VARCHAR(100), @intTable) + ' t'+ CONVERT(VARCHAR(100),@i)
ELSE 
@sqlFrom + ' JOIN T' + CONVERT(VARCHAR(100), @intTable) + ' t'+ CONVERT(VARCHAR(100), @i) + ' ON t'+ CONVERT(VARCHAR(100), @i) + '.DT = t1.DT' 
END
SET @IsFirstTable = 0
SET @i = @i + 1
END
SET @sqlWhere = CONCAT(' WHERE T1.DT BETWEEN ''', dbo.DateString(@Debut), ''' AND ''' , dbo.DateString(@Fin), '''')
SET @sqlOrder = ' ORDER BY T1.DT DESC'
SET @sqlResult = CONCAT(@sqlSelect, @sqlFrom, @sqlWhere, @sqlOrder)
EXEC (@sqlResult)
END