How to get distinct count of values of all the columns of a table based on where condition in sql server?

Question

I have a table with records which has 100 columns, I need to get the count of distinct values of all the columns from this table based on some condition (where clause).

Below query is working fine, but I'm not able to use the where clause. So it's giving the result for all the records of the table. But I want it to be based on some condition lets say column file_id = 1;. My question is how to use where clause with the below query. Or if there is any other alternative way to solve this problem.

declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)

select @SQL = @SQL + case when RowNum = 1 then '' else ' union all ' end
+ ' select ''' + Column_Name + ''' as Column_Name, count(distinct ' + quotename (Column_Name) + ' ) As DistinctCountValue, 
count( '+ quotename (Column_Name) + ') as CountValue FROM ' + quotename (Table_Schema) + '.' + quotename (Table_Name)
from cols
where Table_Name = 'table_name' --print @SQL

execute (@SQL)

I am using the dynamic query because I need to reuse this query for other tables also.

Answer 1

First get the columns and use stuff to generate the select in this way:

SELECT COUNT(ColumnA) AS ColumnA, COUNT(ColumnB AS ColumnB), COUNT(ColumnC) AS ColumnC....

That way you only select on your table once to get all counts, After that, use CROSS APPLY to "unpivot" those columns and return the output on one row per column

CROSS APPLY(
    VALUES(1, 'ColumnA', ColumnA), (2, 'ColumnB', ColumnB), (3, 'ColumnC', ColumnC)
)(ID, ColumnName, DistinctCountValue)

For the filter, use sp_executesql and send the file_id as parameter

exec SP_executesql @SQL, N'@FID INT', @FID = @FileID

Since you are using all columns of the table Row_Number() over(partition by Table_Schema, Table_Name order by ORDINAL_POSITION) as RowNum becomes redundant, ORDINAL_POSITION already has the value that you are looking for

declare @tablename nvarchar(50) = 'MyTestTable'
declare @fileID int = 1
declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName
)

select @SQL = ';WITH  CTE AS (SELECT 
' + 
    STUFF((
    SELECT ', COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') AS ' + QUOTENAME(COLUMN_NAME)
    FROM cols
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
    ), 1, 1, '')
+ '
FROM ' + @TableName + '
WHERE File_ID = @FID
)
SELECT B.*
FROM CTE
CROSS APPLY (
    VALUES ' +STUFF((
    SELECT ',( ' + CAST(ORDINAL_POSITION AS VARCHAR) + ',' + QUOTENAME(COLUMN_NAME,'''') + ',' + QUOTENAME(COLUMN_NAME) + ')'
    FROM cols
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
    ), 1, 1, '') + '
)B (ID,ColumnName,DistinctCountValue) 
'
from cols


exec SP_executesql @SQL, N'@FID INT', @FID = @FileID