CODEWITHSUNDEEP
sql-servert-sqlpivot
F.P
F.P

Reputation: 17831

Count non-empty rows with unknown column names

Given a table that has unknown column names, how can I list all column names and the amount of rows that have a non-empty (NULL or empty string) value in that column?

Small example:

Col1 | Col2 | Col3 | dkasldk | dD? d3# !(
1    |      | 2    |         | 
     | 2    | d    | ddd939  |
f    | f    | 84   |         |

Should yield:

Column Name | Values
Col1        | 2
Col2        | 2
Col3        | 3
dkasldk     | 1
dD? d3# !(  | 0

I already tried around with using INFORMATION_SCHEMA.COLUMNS and creating dynamic SQL queries, but to no avail:

select N'select c.COLUMN_NAME,
(select sum(case when p.' + QUOTENAME(c.COLUMN_NAME) + ' != '''' then 1 else 0 end) from [Produkte] p) [Produkte]
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = ''Produkte'' and c.COLUMN_NAME = ''' + c.COLUMN_NAME + '''' select_statement
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = 'Produkte'

I cannot quite wrap my head around how to combine the two problems (for which I did find solution in their own, but not the combination) of Dynamic Column Names and Count empty values...

Upvotes: 1

Views: 206

Answers (3)

JamieD77
JamieD77

Reputation: 13949

You can do something like this using UNPIVOT..

DECLARE @TableName NVARCHAR(MAX) = N'Produkte',
        @CountColumns NVARCHAR(MAX),
        @Columns NVARCHAR(MAX),
        @Sql NVARCHAR(MAX)

SELECT  @CountColumns = COALESCE(@CountColumns + ',', '') + 'COUNT(NULLIF(' + QUOTENAME(c.COLUMN_NAME) + ','''')) AS ' + QUOTENAME(c.COLUMN_NAME),
        @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(c.COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName

SET     @SQL = 'SELECT [Column Name], [Values] 
                FROM (
                    SELECT '
                        + @CountColumns + '
                    FROM    ' + @TableName + '
                ) t
                UNPIVOT (
                    [Values]
                    FOR [Column Name] IN (' + @Columns + ')
                ) up
'
EXEC(@SQL)

SQL Fiddle Demo

Upvotes: 1

GarethD
GarethD

Reputation: 69789

Ultimately the SQL you are after is something like this:

SELECT  ColumnName, NonEmpty 
FROM (  SELECT  A = 1, 
                [Col1] = COUNT(CASE WHEN [Col1] <> '' THEN 1 END),
                [Col2] = COUNT(CASE WHEN [Col2] <> '' THEN 1 END),
                [Col3] = COUNT(CASE WHEN [Col3] <> '' THEN 1 END),
                [dkasldk] = COUNT(CASE WHEN [dkasldk] <> '' THEN 1 END),
                [dD? d3# !(] = COUNT(CASE WHEN [dD? d3# !(] <> '' THEN 1 END) 
        FROM #T
    ) AS t 
    UNPIVOT 
    (   NonEmpty 
        FOR ColumnName IN ([Col1],[Col2],[Col3],[dkasldk],[dD? d3# !(])
    ) AS upvt;

UNPIVOT Will convert your data from

Col1    Col2    Col3
-------------------------
1       3       2

To the format you require:

ColumnName  NonEmpty
-------------------------
Col1        1
Col2        3
Col3        2

You can build up dynamically using something like this:

-- SAMPLE DATA
USE TempDB;
CREATE TABLE #T 
(   
    [Col1] VARCHAR(1), 
    [Col2] VARCHAR(1), 
    [Col3] VARCHAR(2), 
    [dkasldk] VARCHAR(6), 
    [dD? d3# !(] VARCHAR(1)
);

INSERT #T ([Col1], [Col2], [Col3], [dkasldk], [dD? d3# !(])
VALUES
    ('1', NULL, '2', NULL, ''),
    ('', '2', 'd', 'ddd939', NULL),
    ('f', 'f', '84', NULL, '');


DECLARE @TableName SYSNAME = '#T';

-- VALID INPUT TABLE
IF OBJECT_ID(@TableName, 'U') IS NULL 
    AND OBJECT_ID(@TableName, 'V') IS NULL
BEGIN
    PRINT 'Invalid table or View';
    RETURN
END 

-- BUILD DYNAMIC SQL
DECLARE @SQL NVARCHAR(MAX) = 
    CONCAT('SELECT  ColumnName, NonEmpty FROM (SELECT A = 1, ' ,
            STUFF(( SELECT  CONCAT(',', 
                                    QUOTENAME(name), 
                                    ' = COUNT(CASE WHEN ', 
                                    QUOTENAME(Name), 
                                    ' <> '''' THEN 1 END)')
                    FROM    sys.columns
                    WHERE   [object_id] = OBJECT_ID(@TableName)
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        ' FROM ',
        @TableName,
        ') AS t UNPIVOT (NonEmpty FOR ColumnName IN (',
            STUFF(( SELECT  CONCAT(',', QUOTENAME(name))
                    FROM    sys.columns
                    WHERE   [object_id] = OBJECT_ID(@TableName)
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        ')) AS upvt');

-- EXECUTE DYNAMIC SQL
EXECUTE sp_executesql @SQL;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

You can use a dynamic UNPIVOT.

Using these variables:

DECLARE @qry NVARCHAR(MAX) 
DECLARE @cols NVARCHAR(MAX) = ''

you can select columns names in a format suitable for the UNPIVOT operation with the following dynamic sql:

SELECT @cols = STUFF((SELECT ',[' + c.COLUMN_NAME + ']'
                      FROM INFORMATION_SCHEMA.COLUMNS c
                      WHERE c.TABLE_NAME = 'Produkte'
                      FOR XML PATH('')), 1, 1, '')

Finally use @cols to build the query:

SET @qry = 'SELECT t.Col, COUNT(*) 
            FROM (SELECT Val, Col
                  FROM Produkte
                  UNPIVOT ( 
                     Val FOR Col IN (' + @cols + ')) unpvt
            ) AS t
            WHERE t.Val <> '''' OR t.Val IS NOT NULL
            GROUP BY t.Col'

... and execute it to get desired result:

EXEC(@qry)

Upvotes: 1

Related Questions