CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Hexxed
Hexxed

Reputation: 683

SQL Query on multiple conditional columns

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.

Upvotes: 0

Views: 416

Answers (2)

Mahesh.K
Mahesh.K

Reputation: 901

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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'

Upvotes: 2

Related Questions