SQL Query – SQL Server 2014 – Combine Multiple Colums in Select (Lookup?)

I have this current query (more info than really needed to show):

SELECT DISTINCT
    IBX_Name AS "IBX",
    Tank_ID AS "Main Tank ID",
    Main_Tank_Index AS "Tank Index",
    AST_UST_Level1 AS "Latest Main Tank 1 Level",
    AST_UST_Level2 AS "Latest Main Tank 2 Level",
    AST_UST_Level3 AS "Latest Main Tank 3 Level",
    RunData_Date AS "Last Date"
FROM 
    RunData 
INNER JOIN 
    IBXs ON IBXs.IBX_ID = RunData.fk_IBX_ID
INNER JOIN 
    Tanks ON Tanks.fk_IBX_ID = IBX_ID
WHERE 
    ([RunData_Date] = (SELECT MAX([RunData_Date]) 
                       FROM RunData AS T1 
                       WHERE T1.fk_IBX_ID = IBX_ID 
                         AND (T1.AST_UST_Level1 > 0 OR T1.AST_UST_Level2 > 0 OR T1.AST_UST_Level3 > 0)))

The query returns this output:

IBX        Main Tank ID Tank Index  Latest Main Tank 1 Level Latest Main Tank 2 Level Latest Main Tank 3 Level Last Date
---------- ------------ ----------- ------------------------ ------------------------ ------------------------ ----------
DC2        1            1           16128                    16192                    NULL                     2018-11-08
DC2        2            2           16128                    16192                    NULL                     2018-11-08
DC2        38           NULL        16128                    16192                    NULL                     2018-11-08
DC2        39           NULL        16128                    16192                    NULL                     2018-11-08
DC2        40           NULL        16128                    16192                    NULL                     2018-11-08
DC2        41           NULL        16128                    16192                    NULL                     2018-11-08
DC2        42           NULL        16128                    16192                    NULL                     2018-11-08
DC2        43           NULL        16128                    16192                    NULL                     2018-11-08
DC4        3            1           25881                    26007                    NULL                     2020-03-18
DC4        4            2           25881                    26007                    NULL                     2020-03-18
DC4        5            NULL        25881                    26007                    NULL                     2020-03-18
DC4        6            NULL        25881                    26007                    NULL                     2020-03-18
DC4        7            NULL        25881                    26007                    NULL                     2020-03-18
DC4        8            NULL        25881                    26007                    NULL                     2020-03-18
DC4        9            NULL        25881                    26007                    NULL                     2020-03-18
DC4        10           NULL        25881                    26007                    NULL                     2020-03-18
DC4        11           NULL        25881                    26007                    NULL                     2020-03-18
DC5        12           1           24079                    23774                    NULL                     2020-03-13
DC5        13           2           24079                    23774                    NULL                     2020-03-13
DC5        14           NULL        24079                    23774                    NULL                     2020-03-13
DC5        15           NULL        24079                    23774                    NULL                     2020-03-13
DC5        17           NULL        24079                    23774                    NULL                     2020-03-13
DC5        18           NULL        24079                    23774                    NULL                     2020-03-13
DC5        19           NULL        24079                    23774                    NULL                     2020-03-13
DC5        20           NULL        24079                    23774                    NULL                     2020-03-13
DC5        21           NULL        24079                    23774                    NULL                     2020-03-13

I need to modify this query so that it only shows IBX, Tanks ID (Where Index != 0) and its associated Level.

Level 1 goes to Index 1, Level 2 goes to Index 2, and Level 3 goes to Index 3

I’ve tried all sorts of variations, but I cannot get it to work.

Desired output:

IBX        Main Tank ID Tank Index  Latest Main Tank Level Last Date
---------- ------------ ----------- ---------------------- ----------
DC2        1            1           16128                    2018-11-08
DC2        2            2           16192                    2018-11-08
DC4        3            1           25881                    2020-03-18
DC4        4            2           26007                    2020-03-18
DC5        12           1           24079                    2020-03-13
DC5        13           2           23774                    2020-03-13

Answer

SELECT IBX_Name, Tank_ID, Main_Tank_Index, CASE Main_Tank_Index 
WHEN 1 THEN AST_UST_Level1
WHEN 2 THEN AST_UST_Level2
WHEN 3 THEN AST_UST_Level3 END AS  LatestMainTankLevel, RunData_Date
FROM ...