Only select from tables that are of interest

Question

SQL Server 2016

I have a number of tables

Table A             Table B              Table C                 Table D
 User | DataA        User | DataB         User | DataC            User | DataD
 ===========         ===========          ===================     =============
 1    | 10            1   |  'hello'        4  | '2020-01-01'     1    | 0.34
 2    | 20            2   |  'world'       
 3    | 30           

So some users have data for A,B,C and/or D.

Table UserEnabled            
 User  |  A  |  B  |  C  |  D  
 =============================
  1    |  1  |  1  |  0  |  0
  2    |  1  |  1  |  0  |  0
  3    |  1  |  0  |  0  |  0
  4    |  0  |  0  |  1  |  0

Table UserEnabled indicates whether we are interested in any of the data in the corresponding tables A,B,C and/or D.

Now I want to join those tables on User but I do only want the columns where the UserEnabled table has at least one user with a 1 (ie at least one user enabled). Ideally I only want to join the tables that are enabled and not filter the columns from the disabled tables afterwards.

So as a result for all users I would get

User | DataA |  DataB  | DataC
===============================
  1  |  10   | 'hello' | NULL
  2  |  20   | 'world' | NULL
  3  |  30   |  NULL   | NULL
  4  | NULL  |  NULL   | '2020-01-01'

No user has D enabled so it does not show up in a query

I was going to come up with a dynamic SQL that's built every time I execute the query depending on the state of UserEnabled but I'm afraid this is going to perform poorly on a huge data set as the execution plan will need to be created every time. I want to dynamically display only the enabled data, not columns with all NULL.

Is there another way?

Usage will be a data sheet that may be generated up to a number of times per minute.

Answer 1

Just because I thought this could be fun.

Example

Declare @Col varchar(max) = ''
Declare @Src varchar(max) = ''

Select @Col = @Col+','+Item+'.[Data'+Item+']'
      ,@Src = @Src+'Left Join [Table'+Item+'] '+Item+' on U.[User]=['+Item+'].[User] and U.['+Item+']=1'+char(13)
 From  (
        Select Item
         From  ( Select A=max(A)
                       ,B=max(B)
                       ,C=max(C)
                       ,D=max(D)
                  From  UserEnabled
                  Where 1=1   --<< Use any Key Inital Filter Condition Here
               ) A
         Unpivot ( value for item in (A,B,C,D)) B
         Where Value=1
       ) A

Declare @SQL varchar(max) = '
Select U.[User]'+@Col+'
From  @UserEnabled U
'+@Src

--Print @SQL
Exec(@SQL)

Returns

User DataA  DataB   DataC
1    10     Hello   NULL
2    20     World   NULL
3    30     NULL    NULL
4    NULL   NULL    2020-01-01

The Generated SQL

Select A.[User],A.[DataA],B.[DataB],C.[DataC]
From  UserEnabled U
Left Join TableA A on U.[User]=[A].[User] and U.[A]=1
Left Join TableB B on U.[User]=[B].[User] and U.[B]=1
Left Join TableC C on U.[User]=[C].[User] and U.[C]=1