Query to pull only columns which are relevant or have data for specific where condition

Question

I am trying to write a SQL query (preferably not a stored procedure) to pull specific columns that have data relevant to where condition.

As mentioned in the picture, if I have a where condition on Report column = 'ABC', then the query should return only columns COL1 & COL4, as they only have data for report ABC. Similarly if its Report='PQR' then columns COL2 & COL6.

In a real-life scenario, I have around 90 columns. Preferable need a sub-query or simple SQL statement and not stored procedure as I have to call it from VB app.

Here is a test table

    create table report_test (
        report varchar(100),
        col1 int,
        col2 varchar(100),
        col3 int,
        col4 varchar(100),
        col5 varchar(100),
        col6 varchar(100)
    );
    insert into report_test (report, col1, col4) values ('abc', 1, '1');
    insert into report_test (report, col2, col6) values ('pqr', '1', '1');
    insert into report_test (report, col3) values ('xyz', 1);
    insert into report_test (report, col1, col4) values ('abc', 1, '1');
    insert into report_test (report, col2, col6) values ('pqr', '1', '1');
    insert into report_test (report, col3) values ('xyz', 1);

Thanks in advance.

@GordonLinoff - It should return as many columns as long as they have data in any of its row. — Nikita, Jun 11 '20 at 01:41
@Nikita does that mean, you want SQL to check every column to see if it is NULL. If it is not NULL, you want to select that column? For example, if report ABC had data in COL1, 2, 3 and 6, you want SQL to figure that out and then do `select col1, col2, col3, col6 from tablename where report = 'abc'`? — zedfoxus, Jun 11 '20 at 01:46
You'll need a stored procedure and it will be ugly. How many records do you have in reports table? Do you looking for only SQL for 1 report at a time? VB can call stored proc, I'd believe. — zedfoxus, Jun 11 '20 at 01:51
@zedfoxus around 1 million records and yes it would need to pull for 1 report at a time — Nikita, Jun 11 '20 at 01:54
I'll use dynamic SQL. Unpivot the top 1 row to find out which column has value and then select those columns. And yes, VB should be able to use a stored procedure. — Weihui Guo, Jun 11 '20 at 02:22
@WeihuiGuo can you pls provide an example on how I can do unpivot 1st row. Another option I was thinking of to get max of all columns in sub-query and return only columns that have any value else they'll show null and i'll disregard them. Is it feasible and how to achieve that together for all column instead of grouping based on few cols? — Nikita, Jun 11 '20 at 13:36
@Nikita I added a simplified version. You'll use dynamic SQL to get all the 90 columns instead of typing them all. — Weihui Guo, Jun 11 '20 at 16:26
@Nikita your question got some good answers. Did any of them help? I'd encourage you to put closure to your question by marking one of the answers as accepted. You can do that by clicking on a tick mark to the left of your chosen answer. — zedfoxus, Jun 11 '20 at 18:21
@WeihuiGuo I am pretty close to getting the result using your solution. Only place where its causing issue is during unpivot, if columns are or different type its throwing error. Error: "The type of column "STATE" conflicts with the type of other columns specified in the UNPIVOT list. How can this be resolved? — Nikita, Jun 11 '20 at 19:56

score 0 · Answer 1 · answered Jun 11 '20 at 03:33

Here's a stored proc that can get you what you want.

Table example

create table report_test (
    report varchar(100),
    col1 varchar(100),
    col2 varchar(100),
    col3 varchar(100),
    col4 varchar(100),
    col5 varchar(100),
    col6 varchar(100)
);
insert into report_test (report, col1, col4) values ('abc', '1', '1');
insert into report_test (report, col2, col6) values ('pqr', '1', '1');
insert into report_test (report, col3) values ('xyz', '1');

Stored procedure

Comments are inline. The basic idea is this:

ask the metadata table information_schema all the columns for the report table
loop through each column
ask the report table whether that column for a given report is not null
if not null, add it as a column to select from
execute the final query with report and selected columns (that were not null)

Now, the procedure.

create procedure getReportInfo
    @report varchar(100)
as
begin

    -- holds name of the column as each column is checked
    declare @col nvarchar(100);

    -- holds 1 or 0 - 1 means column was not null for that report
    declare @cnt int;

    -- this is the SQL that asks DB whether a given column is not null in the database
    declare @colSQL nvarchar(max);

    -- holds parameter definition for dynamic queries
    declare @parameter_definition nvarchar(1000);

    -- this is the final SQL that will be executed
    declare @s nvarchar(1000);
    set @s = 'select report';

    declare c cursor read_only for
        select column_name from INFORMATION_SCHEMA.columns
        where table_name = 'report_test' and column_name <> 'report'
        order by ORDINAL_POSITION;

    open c;

    fetch next from c into @col;
    while @@FETCH_STATUS = 0
    begin

        -- ask DB whether column was not null in the table for a given report
        set @cnt = 0;
        set @colSQL = concat(
            'select @cnt_out = count(*) from report_test where report = @rep_temp and ',
            @col, ' is not null'
        );
        set @parameter_definition = N'@rep_temp nvarchar(100), @cnt_out int OUTPUT';
        execute sp_executesql @colSQL,
            @parameter_definition,
            @rep_temp = @report,
            @cnt_out = @cnt output;

        -- if column was not null, add it as a selectable field in the final query
        if @cnt > 0
        begin
            set @s = concat(@s, ', ', @col);
        end;

        fetch next from c into @col;
    end;

    close c;
    deallocate c;

    -- execute final query
    set @s = concat(@s, ' from report_test where report = @rep_temp');
    set @parameter_definition = N'@rep_temp nvarchar(100)';
    execute sp_executesql @s,
        @parameter_definition,
        @rep_temp = @report;

end
go

Execute the procedure

execute getReportInfo 'pqr'
report  col2    col6
pqr       1      1


execute getReportInfo 'abc'
report  col1    col4
abc      1        1

execute getReportInfo 'xyz'
report  col3
xyz      1

This is just an idea. Depending on what you have in your report table, you will have to tweak it. There're a few concepts in this example:

how to create a stored proc
how to send parameters to it
how to use cursors
how to use dynamic queries
how to send parameters to dynamic queries
how to retrieve output from a dynamic query
how to execute a stored procedure

Calling a stored proc from VB

See these articles:

score 0 · Answer 2 · answered Jun 11 '20 at 04:14

You can write a query with separate result set for each filter condition and apply UNION ALL for them as given below:

SELECT Col1 AS Colum1, col4 AS Column2
FROM TableName
WHERE Report = 'ABC'
UNION ALL
SELECT Col2 AS Colum1, col6 AS Column2
FROM TableName
WHERE Report = 'PQR'
.
.
.

Query to pull only columns which are relevant or have data for specific where condition

2 Answers2

Table example

Stored procedure

Execute the procedure

Calling a stored proc from VB