将函数应用于表和列的列表

Question

我想按日期为一组表中的每个表计算表中的行以及满足条件的行。每个表的日期列都有不同的名称。

实现这一点的一个简单方法是：

SELECT 'TableA' AS 'TableName', [AsOfDate], COUNT(*) AS 'Rowcount', SUM(IIF([X] IS NULL,0,1)) AS 'NonEmpty'
FROM TableA GROUP BY [AsOfDate]
UNION ALL
SELECT 'TableB' AS 'TableName', [Snapshot Date], COUNT(*) AS 'Rowcount', SUM(IIF([X] IS NULL,0,1)) AS 'NonEmpty'
FROM TableB GROUP BY [Snapshot Date]
...UNION ALL {TableC,D,E...}

如何从一个表名和相关列的表开始完成上述操作？像这样：

WITH Tables AS ( SELECT * FROM ( VALUES
('TableA', 'AsOfDate'),
('TableB', 'Snapshot Date'),
...
('TableZ', 'Date of Record')
) AS (Tables([Table],[DateColumn]) )
SELECT MyFn([Table],[DateColumn]) FROM Tables

产量

[Table]    [Date]    [Rows]    [NonEmpty]
TableA     2022-01-01    20    18
TableA     2022-01-02    20    19
TableA     2022-01-03    20     0
TableB     2022-01-01    30    28
...

我原以为在一个使用表名和列名的函数中执行动态SQL就可以了，但显然这是不可能的。什么是DRY解决方案？

Answer 1

这里有一个简单的方法示例，可以根据提供的名称/列表使用string_agg构建动态SQL，前提是您使用的是最新版本(V14+(的SQL Server。只需按照相同的模式添加其他列。。。

declare @sql nvarchar(max);
with t as ( 
select n, d from (
values 
('TableA', 'AsOfDate'),
('TableB', 'Snapshot Date'),
('Tablez', 'date of Record')
)v(n, d)
)
select 
@sql =
String_Agg(
Concat(
'Select ''', n, ''' as TableName, ',
QuoteName(d), ', Count(*) as rowcount from ',
QuoteName(n), ' group by ', 
QuoteName(d)
), Concat(' union all', Char(13))
)
from t;
print @sql;

输出：

Select 'TableA' as TableName, [AsOfDate], Count(*) as rowcount from [TableA] group by [AsOfDate] union all
Select 'TableB' as TableName, [Snapshot Date], Count(*) as rowcount from [TableB] group by [Snapshot Date] union all
Select 'Tablez' as TableName, [date of Record], Count(*) as rowcount from [Tablez] group by [date of Record]

Answer 2

这里有一种方法可以做到这一点。我知道大多数人都会使用游标或while循环，但实际上并不需要它。由于您的示例数据没有X列，所以我省略了这一部分，让您计算出这一部分。给定表名和日期列，类似这样的内容应该是关闭的。如果日期列是日期时间列，则可能需要将它们转换为日期，以便按预期进行分组。

declare @sql nvarchar(max) = '';
with cte as
(
SELECT * FROM 
( VALUES
('TableA', 'AsOfDate'),
('TableB', 'Snapshot Date'),
('TableZ', 'Date of Record')
) x(TableName, DateColumn)
)
select @sql = string_agg('select TableName = ' + quotename(c.TableName, '''') + ', MyDate = ' + quotename(c.DateColumn) + ', count(*) from ' + quotename(c.TableName) + ' group by ' + quotename(c.DateColumn), ' union all ')
from cte c

--use this to view the dynamic sql created
select @sql
--once you are satisfied the dynamic is accurate uncomment the line below to execute it
--exec sp_executesql @sql

Answer 3

STRING_AGG答案对我不起作用，因为我一直使用SQL Server 2016，但它们确实帮助我搜索并找到了一种有效的旧语法：

DECLARE @SQL NVARCHAR(MAX)
WITH
CTE_TableName AS (
SELECT 0 AS Field0, TableName, DateColumn
FROM ( VALUES
('TableA','AsOfDate'),
('TableB','Snapshot Date'),
('TableZ','EFFDATE')
) x(TableName, DateColumn)
),
SqlConcat AS (SELECT TOP 1 (
SELECT 'SELECT ' + QUOTENAME(t1.TableName,'''') + ' AS ''TableName'', ' + QUOTENAME(t1.DateColumn,'[]') + ' AS ''Date'','
+ 'COUNT(*) AS ''Rows'','
+ 'SUM(IIF(COALESCE(CommonColumnA, CommonColumnZ) IS NULL,0,1)) AS ''HasValue'''
+ ' FROM ' + QUOTENAME(t1.TableName,'[]')
+ ' GROUP BY ' + QUOTENAME(t1.DateColumn,'[]')
+ ' UNION ALL '
FROM CTE_TableName t1
WHERE t1.Field0 = t0.Field0
FOR XML PATH('')) AS SqlConcat
FROM CTE_TableName t0
GROUP BY t0.Field0, t0.TableName, t0.DateColumn
)
SELECT @SQL = (
'WITH dateRange as (
SELECT CAST(DATEADD(DAY, -35, GETDATE()) AS DATE) AS ''Date''
UNION ALL
SELECT DATEADD(DAY,1,[Date])
FROM dateRange
WHERE DATEADD(DAY,1,[Date]) < GETDATE()
)' +
'SELECT [TableName], dateRange.[Date], [Rows], [HasValue] FROM (' + 
--Trim the final 'UNION ALL'
(SELECT LEFT(SqlConcat, LEN(SqlConcat)-9) FROM SqlConcat) + 
') AS QrySql RIGHT JOIN dateRange ON QrySQL.[Date] = dateRange.[Date] WHERE NOT [TableName] IS NULL' +
'ORDER BY [TableName], [Date] DESC'
) 
--SELECT @SQL
EXEC sp_executesql @SQL