While every SQL implementation offers some form of value parameterization, no such facility exists to parameterize object identifiers (e.g. table names, column names, etc) - which means you must resort to Dynamic-SQL, which introduces its own risks (namely SQL injection).
With your specific problem, we can start by trying to solve it without Dynamic-SQL, by assuming a known and fixed set of tables to query, then we can convert it to Dynamic-SQL, hopefully in a safe manner:
SELECT
'Table1' AS TableName
[Year],
COUNT(*) AS YearRowCount
FROM
Table1
GROUP BY
[Year]
UNION ALL
SELECT
'Table2' AS TableName
[Year],
COUNT(*) AS YearRowCount
FROM
Table2
GROUP BY
[Year]
UNION ALL
...
Hopefully you're seeing a pattern here.
This query, so far, will give us results of this form:
TableName Year YearRowCount
'Table1' 2017 1234
'Table1' 2016 2345
'Table1' 2015 3456
'Table1' 2014 1234
'Table1' 2013 1234
'Table1' 2011 1234
'Table2' 2017 1234
'Table2' 2016 2345
'Table2' 2015 3456
'Table2' 2013 1234
'Table2' 2012 1234
'Table2' 2011 1234
...
We can then use PIVOT to transpose the rows into columns. PIVOT (and UNPIVOT) do require you to explicitly name each column to be transposed, unfortunately - but it would be nice if they had a PIVOT ALL feature or something).
SELECT
tableName,
YearRowCount,
[2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
-- our UNION query goes here --
)
PIVOT
(
SUM( YearRowCount )
FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)
So now we know the pattern of the inner query and the PIVOT statement to surround it, we can make it dynamic.
There are 3 approaches for generating Dynamic SQL on a "for each row..." basis. The first is to use a CURSOR, the second is to use some kind of T-SQL loop (WHILE, etc) - both of these approaches take the iterative approach - but there's a 3rd version which is more functional and syntactically simpler. I'll demonstrate this functional approach.
Also, we can avoid the uglier parts of manual string concatenation by using (abusing) the FORMATMESSAGE function which serves as a sprintf implementation. To use FORMATMESSAGE to format a string requires SQL Server 2016 or later (though Compatibility Level does not need to be 130 as far as I can tell). If you are running an earlier version you'll need to use CONCAT or 'foo' + @var + 'bar'-style concatenation.
I'm also using the COALESCE( [aggregate] + [separator], '' ) + [value] trick described in this answer: https://stackoverflow.com/a/194887/159145 - it's one method of concatenating (aggregating) row values, though it feels a bit ugly. Remember that SQL is primarily concerned with relational algebra of unordered sets of tuple data (i.e. tables), which does not normally cover view-level concerns like ordering or aggregating sorted data - which is what concatenation is.
DECLARE @unionTemplate varchar(1024) = '
SELECT
''%s.%s'' AS TableName
[Year],
COUNT(*) AS YearRowCount
FROM
[%s].[%s]
GROUP BY
[Year]
'
DECLARE @unionSeparator varchar(20) = '
UNION ALL
'
DECLARE @unionQuery varchar(max)
SELECT
@unionQuery = COALESCE( @unionQuery + @unionSeparator, '' ) + FORMATMESSAGE( @unionTemplate, SCHEMA_NAME, TABLE_NAME, SCHEMA_NAME, TABLE_NAME )
FROM
INFORMATION_SCHEMA.TABLES
ORDER BY
SCHEMA_NAME,
TABLE_NAME
Anyway, this query will generate the query stored in @unionQuery, so now we just need to compose it...
DECLARE @pivotQuery varchar(max) = '
SELECT
tableName,
YearRowCount,
[2011], [2012], [2013], [2014], [2015], [2016], [2017]
FROM
(
%s
)
PIVOT
(
SUM( YearRowCount )
FOR [Year] IN ( 2011, 2012, 2013, 2014, 2015, 2016, 2017 )
)'
SET @pivotQuery = FORMATMESSAGE( @pivotQuery, @unionQuery )
...and execute it (EXEC sp_executesql is preferred over the archaic EXEC()) - also note that EXEC() is not the same thing as EXEC!
EXEC sp_executesql @pivotQuery
ta-da!
Older SQL Server versions (2014, 2012, 2008 R2, 2008):
These are untested, but if you need to run on SQL Server versions older than 2016 (v13.0), try these alternatives to FORMATMESSAGE:
DECLARE @unionQuery nvarchar(max)
SELECT
@unionQuery =
COALESCE( @unionQuery + ' UNION ALL ', '' ) +
CONCAT(
'SELECT ''',
SCHEMA_NAME, '.', TABLE_NAME, '[Year],
COUNT(*) AS YearRowCount
FROM
[', SCHEMA_NAME, '].[', TABLE_NAME, ']
GROUP BY
[Year]
'
)
FROM
INFORMATION_SCHEMA.TABLES
ORDER BY
SCHEMA_NAME,
TABLE_NAME
As the @pivotQuery is only inserted once, it's okay to use REPLACE to insert the inner @unionQuery, but never do this when dealing with user-provided values because you open yourself up to SQL injection-like attacks:
SET @pivotQuery = REPLACE( @pivotQuery, '%s', @unionQuery )
