asked
Seeking help on query
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 GAP_REFERENCE_PR1_1 1 2 GAP_REFERENCE_PR1_2 1 3 GAP_REFERENCE_PR1_3 ................................................................................................................ I need help in writing the most efficient SQL for getting the above results from the above two tables. Thanks in advance.
I have tried the following, but do not know if this is the best possible solution: 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 think the most efficient method would first involve editing your tables. Get rid of the repeated GAP_REFERENCE_PR1_XX columns and put them into a new table like this: CREATE TABLE [dbo].[TAB3] ( [COIL_ID] [varchar](25) NOT NULL , [GAP_REFERENCE_Num] [integer] NULL , [GAP_REFERENCE_Value] [float] NULL, ) Once you do that I think you just join them all three tables and everything is easy
21 People are following this question.
