sql_lover sql_lover - 5 months ago 10
SQL Question

Doesnt display the right count in dynamic sql

Here is the input table

Input

I cannot get the right count of the sku. I need to achieve this by this dynamic sql. It gives result like below:
Present Result

SET @SQL = 'SELECT DISTINCT
a.'+@primary_column+' AS [No. of distinct'' '+@primary_column+']
,sum(case when '+@secondary_column+' is null or '+@secondary_column+' = '''' then 0 else 1 end) AS ''Total_Count''
,STUFF((SELECT DISTINCT '', ['' +'+@secondary_column+'+'']''
FROM ['+@TableName+'] b
WHERE b.'+@primary_column+' = a.'+@primary_column+'
FOR XML PATH('''')),1, 1, '''') AS '+@secondary_column+'

FROM ['+@TableName+'] a
GROUP BY '+@primary_column+'';

PRINT(@SQL)
EXEC(@SQL)


This is the expected output

expected output

Answer

Change this:

,sum(case when '+@secondary_column+' is null or '+@secondary_column+' = ''''  then 0 else 1 end) AS ''Total_Count''

On this:

,COUNT(DISTINCT '+@secondary_column+') AS ''Total_Count''

Full query:

DECLARE @sql nvarchar(max),
        @secondary_column nvarchar(max) = 'Parent_sku',
        @primary_column nvarchar(max) = 'category',
        @TableName nvarchar(max) = 'SomeTable'


SELECT @sql = '
SELECT  ' + @primary_column + ' AS [No. of distinct'' ' + @primary_column+ '],
        COUNT(DISTINCT ' + @secondary_column + ') Total_count,
        STUFF((SELECT DISTINCT '','' + QUOTENAME(' + @secondary_column + ') 
                FROM ' + @TableName + ' s 
                WHERE s.' + @primary_column+ ' = a.' + @primary_column+ ' 
                FOR XML PATH('''')),1,1,'''') as ' + @secondary_column + '
FROM ' + @TableName + ' a
GROUP BY ' + @primary_column 

PRINT @sql

EXEC sp_executesql @sql

Output:

No. of distinct' category   Total_count Parent_sku
                            1           [TB004]
Kids                        1           [TB003]
Men                         1           [TB001]
Women                       1           [TB002]