从表SQL Server中的每个列获取每个值的计数

从表SQL Server中的每个列获取每个值的计数

您可以使用：

DECLARE @Table SYSNAME = 'TableName'; DECLARE @SQL NVARCHAr(MAX) = ''SELECt @SQL = STUFF((SELECT ' UNIOn SELECT ''' + name       + ''' AS [Column], '       + 'CAST(' + QUOTENAME(Name)      + ' AS NVARCHAr(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM '       + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name)         FROM   sys.columns          WHERe  object_id = Object_id(@Table)         -- concatenate result strings with FOR XML PATH         FOR XML PATH ('')), 1, 7, '');EXECUTE sp_executesql @SQL;

对于具有两列（Column1和Column2）的表，将产生如下所示的SQL

SELECt 'Column1' AS [Column],         CAST([Column1] AS NVARCHAr(MAX)) AS [ColumnValue],         COUNT(*) AS [Count] FROM    [TableName] GROUP BY [Column1] UNIOn SELECt  'Column2' AS [Column],         CAST([Column2] AS NVARCHAr(MAX)) AS [ColumnValue],         COUNT(*) AS [Count] FROM    [TableName] GROUP BY [Column2]

---

编辑

如果要为每个列提供新的结果集，请使用：

DECLARE @Table SYSNAME = 'TableName'; DECLARE @SQL NVARCHAr(MAX) = '';SELECt @SQL = (SELECT ' SELECT ' + QUOTENAME(Name)   + ', COUNT(*) AS [Count] FROM '   + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name) + ';'     FROM   sys.columns      WHERe  object_id = Object_id(@Table)     -- concatenate result strings with FOR XML PATH     FOR XML PATH (''));EXECUTE sp_executesql @SQL;

会产生如下的SQL：

SELECt  [Column1],         COUNT(*) AS [Count] FROM    [callsupplier] GROUP BY [Column1];SELECt  [Column2],         COUNT(*) AS [Count] FROM    [callsupplier] GROUP BY [Column2];