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.

Answer 1

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:

• VB6: https://www.codeproject.com/Articles/15222/How-to-Use-Stored-Procedures-in-VB6
• VB.NET: SQL Server stored procedure and execute in VB.NET

Answer 2

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'
.
.
.