Alternative to CASE in SQL Server

Question

I have the following two tables:

 CREATE TABLE [dbo].[TAB1](
     [COIL_ID] [varchar](25) NOT NULL,
     [COIL_DATE] [datetime] NULL,
     [GRADE] [float] NULL,
     [QUALITY] [float] NULL,
     [FURNACE_NUM] [float] NULL,
     [FM_GAUGE] [float] NULL,
     [FM_WIDTH] [float] NULL,
     [FINTEMP_AIM] [float] NULL,
     [SLAB_GAUGE] [float] NULL,
     [SLAB_WIDTH] [float] NULL,
     [SLAB_LENGTH] [float] NULL,
     [DO_TEMP] [float] NULL,
     [GAP_REFERENCE_PR1_1] [float] NULL,
     [GAP_REFERENCE_PR1_2] [float] NULL,
     [GAP_REFERENCE_PR1_3] [float] NULL,
     [GAP_REFERENCE_PR1_4] [float] NULL,
     [GAP_REFERENCE_PR1_5] [float] NULL,
     [GAP_REFERENCE_PR1_6] [float] NULL,
     [GAP_REFERENCE_PR1_7] [float] NULL,
     [GAP_REFERENCE_PR1_8] [float] NULL,
     [GAP_REFERENCE_PR1_9] [float] NULL,
     [GAP_REFERENCE_PR1_10] [float] NULL,
     [GAP_REFERENCE_PR1_11] [float] NULL,
     [GAP_REFERENCE_PR1_12] [float] NULL,
     [GAP_REFERENCE_PR1_13] [float] NULL,
     [GAP_REFERENCE_PR1_14] [float] NULL,
     [GAP_REFERENCE_PR1_15] [float] NULL,
     [GAP_REFERENCE_PR1_16] [float] NULL,
     [GAP_REFERENCE_PR1_17] [float] NULL,
     [GAP_REFERENCE_PR1_18] [float] NULL,
     [GAP_REFERENCE_PR1_19] [float] NULL,
     [GAP_REFERENCE_PR1_20] [float] NULL,
     [GAP_REFERENCE_PR1_21] [float] NULL,
     [GAP_REFERENCE_PR1_22] [float] NULL,
     [GAP_REFERENCE_PR1_23] [float] NULL,
     [GAP_REFERENCE_PR1_24] [float] NULL,
     [GAP_REFERENCE_PR1_25] [float] NULL
 ) 


 CREATE TABLE [dbo].[TAB2](
     [COIL_ID] [varchar](25) NOT NULL,
     [_SegmentNr] [int] NOT NULL,
     [HRM Metal In Mill] [real] NULL,
     [HRM Screws Homed] [real] NULL,
     [HRM Tracking Current Pass Number] [real] NULL,
     [Topmotor_ArmCur] [real] NULL,
     [Max_Topmtr_Armcur_pass] [real] NULL,
     [Botmotor_ArmCur] [real] NULL,
     [Max_Botmtr_Armcur_pass] [real] NULL,
     [SPEED] [real] NULL,
     [POWER] [real] NULL,
     [TEMP] [real] NULL,
     [T_Load] [real] NULL,
     [D_Load] [real] NULL,
     [PosFb] [real] NULL,
     [Dif_Pos] [real] NULL,
     [Guide_Entry] [real] NULL,
     [Guide_Exit] [real] NULL,
     [Thread_Achi] [real] NULL
 ) 

There will one row in [dbo].[TAB1] for a coild-id and multiple rows in [dbo].[TAB2].

The out put I need is as follows:

COIL-ID  HRM-Tracking-Current-Pass-Number      GAP
 1             1                              value of GAP_REFERENCE_PR1_1                     
 1             2                              value of GAP_REFERENCE_PR1_2                         
 1             3                              value of GAP_REFERENCE_PR1_3

I am currently using the following code:

select a.coil_id Coil_ID, b.[HRM Tracking Current Pass Number] ,
max(case b.[HRM Tracking Current Pass Number]
when 1   THEN a.EXIT_GAUGE_PR1_1
when 2   THEN a.EXIT_GAUGE_PR1_2
when 3   THEN a.EXIT_GAUGE_PR1_3
when 4   THEN a.EXIT_GAUGE_PR1_4
when 5   THEN a.EXIT_GAUGE_PR1_5
when 6   THEN a.EXIT_GAUGE_PR1_6
when 7   THEN a.EXIT_GAUGE_PR1_7
when 8   THEN a.EXIT_GAUGE_PR1_8
when 9   THEN a.EXIT_GAUGE_PR1_9
when 10  THEN a.EXIT_GAUGE_PR1_10
when 11  THEN a.EXIT_GAUGE_PR1_11
when 12  THEN a.EXIT_GAUGE_PR1_12
when 13  THEN a.EXIT_GAUGE_PR1_13
when 14  THEN a.EXIT_GAUGE_PR1_14
when 15  THEN a.EXIT_GAUGE_PR1_15
when 16  THEN a.EXIT_GAUGE_PR1_16
when 17  THEN a.EXIT_GAUGE_PR1_17
when 18  THEN a.EXIT_GAUGE_PR1_18
when 19  THEN a.EXIT_GAUGE_PR1_19
when 20  THEN a.EXIT_GAUGE_PR1_20
when 21  THEN a.EXIT_GAUGE_PR1_21
when 22  THEN a.EXIT_GAUGE_PR1_22
when 23  THEN a.EXIT_GAUGE_PR1_23
when 24  THEN a.EXIT_GAUGE_PR1_24
when 25  THEN a.EXIT_GAUGE_PR1_25
END)  as EXIT_GAUGE,

I want to know if there is a more efficient way writing the query.

Thanks in advance for the help.

Answer 1

Starting in SQL Server 2012, there is a CHOOSE method:

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;  

Result  
-------------  
Developer  

So in your case, you could probably use it like this:

SELECT MAX(
    CHOOSE(
        b.[HRM Tracking Current Pass Number],
        a.EXIT_GAUGE_PR1_1,
        a.EXIT_GAUGE_PR1_2,
        a.EXIT_GAUGE_PR1_3,
        ...
    )
)