SQL Query on multiple conditional columns

Question

Pardon for the title I cant seem to figure out what could I do. I have a MainTable that have some columns that is defined in another table DefTable

Simplified because this columns runs from Col1 to Col99

MainTable

RowId Col1 Col2 Col3 Col4
----- ---- ---- ---- ----
  1    Y   N    N    N
  2    N   Y    N    N
  3    N   N    N    Y

DefTable

Key Description
--- -----------
 1  Logistics
 2  Warehouse
 3  Packaging
 4  MainLobby

Condition is:
If a column is tagged Y I should display the Description according to its key in the DefTable

Also I'm looking into the possibility of two or more Columns are ticked Y. If that happens. I should Concatenate the results like Logistics, MainLobby

Expected output

RowId Description
----- -----------
  1   Logistics
  2   Warehouse
  3   MainLobby

Possibility of multiple ticked columns

RowId Description
----- -----------
  1   Warehouse, MainLobby

I have been tinkering this answer but I'm still stuck. Please help me.

Yogesh Sharma · Answer 1 · 2018-02-28T07:35:04.090

2

You would use Apply operator (i.e. CROSS APPLY)

SELECT a.RowId, d.Description 
FROM MainTable  t
CROSS APPLY (
       VALUES (t.RowId, 1, t.Col1), (t.RowId, 2, t.Col2),
              (t.RowId, 3, t.Col3), (t.RowId, 4, t.Col4)
              ....
)a(RowId, Id, Cols)
INNER JOIN DefTable d ON d.[Key] = a.Id
WHERE a.Cols = 'Y'

edited Feb 28 '18 at 07:35

answered Feb 28 '18 at 07:01

Yogesh Sharma

49,870
5
26
52

Yes this is acceptable when Im only limited to 4 items. but i got around 99 columns and possibly go more than 99. Wanting to to be more dynamic and what if I have more than 2 fields tagged `Y` – Hexxed Feb 28 '18 at 07:16
Joining MainTable and DefTable on RowId doesn't seem fine. Please check. – DatabaseCoder Feb 28 '18 at 07:31
@Hexxed... If you don't have periodically change the columns then this approach would useful else you would look into more dynamic way. – Yogesh Sharma Feb 28 '18 at 07:39
@YogeshSharma thank you this solution is helpful too – Hexxed Mar 01 '18 at 03:04

Mahesh.K · Accepted Answer · 2018-02-28T08:05:03.300

I guess this is what you are looking for .

Your Data Looks like :

MainTable

 SELECT *  INTO #MainTable FROM (
  VALUES(1,'Y','N','N','N'),
  (2,'N','Y','N','N'),
  (3,'N','N','N','Y')
  ) AS A(RowId ,Col1 ,Col2 ,Col3 ,Col4)

DefTable

 SELECT * INTO #DefTable FROM (
 VALUES(1,'Logistics'),
 (2,'Warehouse'),
 (3,'Packaging'),
 (4,'MainLobby')
 )AS A([Key],[Description])

Query:

 SELECT M.RowId ,
 CASE WHEN (Col1='Y' OR Col2='Y' OR Col3='Y' OR Col4='Y' )THEN D.Description ELSE '' END AS [Description] 
 FROM #MainTable M
 INNER JOIN #DefTable D ON D.[Key]=M.RowId

Dynamic Method:

DECLARE @QUERY NVARCHAR(MAX),@COLUMNSQUERY NVARCHAR(MAX)

SET @COLUMNSQUERY=(SELECT 'CASE '+(SELECT  'WHEN '+COLUMN_NAME +'=''Y'' THEN [DESCRIPTION] ' 
    FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like '#MainTable%' 
    and COLUMN_NAME <>'RowId' for xml path('') ) + 'ELSE '''' END AS [DESCRIPTION]' )

SET @QUERY=' SELECT M.RowId ,'+@COLUMNSQUERY + 'FROM #MainTable M
INNER JOIN #DefTable D ON D.[Key]=M.RowId'

EXEC (@QUERY)

Note : I've picked the columns of temp table from Temp db . Please replace it with your actual database where your main table exists

Replace this condition FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like '#MainTable%' with this FROM [YourDatabaseName].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME like 'Your Main table Name%'

Yes this is acceptable when Im only limited to 4 items. but i got around 99 columns and possibly go more than 99. — Hexxed, Feb 28 '18 at 07:15
I'e added the `Dynamic Method` as well . Now ,check the updated answer — Mahesh.K, Feb 28 '18 at 07:51
Now you no need to hard code all of your 99+ columns in the code .you can simply use that dynamic code . — Mahesh.K, Feb 28 '18 at 08:02

SQL Query on multiple conditional columns

2 Answers2