Apply function to a list of tables and columns-CodePudding

I want to count the rows in a table, and the rows that satisfy a condition, for each table in a set of tables, by date. Each table's date column goes by a different name.

A simple way of achieving this is:

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...}

How can I accomplish the above starting with a table of table names and pertinent columns? Like this:

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

Yielding

[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
...

I thought executing dynamic SQL in a function taking the table name and column name would do the trick, but apparently that's not possible. What's a DRY solution?

CodePudding user response：

Here is one way you can do this. I know that most people would go to a cursor or while loop but it really isn't needed. Since your sample data didn't have the column X, I left that off and will let you figure that part out. Given the table names and the date column something like this should be close. If your date columns are datetime columns you may have to convert them to a date do the grouping works as expected.

declare @sql nvarchar(max) = '';

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

--this removes the last ' union all '
select @sql = left(@sql, len(@sql) - 10)

--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

CodePudding user response：

Here's an example of a simple way to construct your dynamic SQL using string_agg based on your supplied table of names/column(s), provided you're using a recent (V14 ) version of SQL Server. Just follow the same pattern to add additional columns...

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;

Output:

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]