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.

Assuming a typo, but based on the desired results, C4 should be 1 — John Cappelletti, Jan 31 '20 at 00:04

score 2 · Answer 1 · answered Jan 30 '20 at 22:43

You have no choice but to approach this through dynamic SQL. A select query has a fixed set of columns defined when the query is created. No such thing as "variable" columns.

What can you do? One method is to "play a trick". Store the columns as JSON (or XML) and delete the empty columns.

Another method is to create a view that has the specific logic you need. I think you can maintain this view by altering it in a trigger, based on when data in the enabled table changes. That said, altering the view requires dynamic SQL so the code will not be pretty.

John Cappelletti · Accepted Answer · 2020-01-31T00:09:54.957

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

score 0 · Answer 3 · answered Jan 30 '20 at 23:03

If all the relations are 1:1, you can make one query with

...
FROM u 
LEFT JOIN a ON u.id = a.u_id
LEFT JOIN b ON u.id = b.u_id
LEFT JOIN c ON u.id = c.u_id
LEFT JOIN d ON u.id = d.u_id
...

and use display logic on the client to omit the irrelevant columns.

If more than one relation is 1:N, then you'd likely have to do multiple queries anyway to prevent N1xN2 results.

Only select from tables that are of interest

3 Answers3