Using the selection_helper table I want to populate the bet_helper table.
The relationship between the two tables is bet_id.
What I need to do is spread the values of the selection_helper.odds column by bet_id
across the columns bet_helper.odds1 to bet_helper.odds8.
The quantity of selection_helper.odds by bet_id values will range from 1 to 8.
A further caveat is that the columns bet_helper.odds1 to bet_helper.odds8 should only be populated with
the value of selection_helper.odds if the value of selection_helper.result_id = 1, else if
the value of selection_helper.result_id = 2 or 3 then they should be populated with a 0.
Any out of scope odds1 - odds8 columns should be NULL.
Below is an example of what bet_id 1 and 2 should look like in the table bet_helper.
bet_id odds1 odds2 odds3 odds4 odds5 odds6 odds7 odds8
1 1.40000 0 0 1.40000 0 0 NULL NULL
2 0 2.25000 0 0 0 2.20000 NULL NULL
CREATE TABLE [dbo].[bet_helper](
[bet_id] [int] NOT NULL,
[odds1] [decimal](8, 5) NULL,
[odds2] [decimal](8, 5) NULL,
[odds3] [decimal](8, 5) NULL,
[odds4] [decimal](8, 5) NULL,
[odds5] [decimal](8, 5) NULL,
[odds6] [decimal](8, 5) NULL,
[odds7] [decimal](8, 5) NULL,
[odds8] [decimal](8, 5) NULL
)
CREATE TABLE [dbo].[selection_helper](
[betNo] [int] NULL,
[bet_id] [int] NOT NULL,
[odds] [decimal](8,5) NOT NULL,
[result_id] [int] NOT NULL
)
INSERT INTO [dbo].[selection_helper] VALUES (1,1,1.40000,1)
INSERT INTO [dbo].[selection_helper] VALUES (2,1,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (3,1,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (4,1,1.40000,1)
INSERT INTO [dbo].[selection_helper] VALUES (5,1,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (6,1,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (7,2,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (8,2,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (9,2,2.25000,1)
INSERT INTO [dbo].[selection_helper] VALUES (10,2,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (11,2,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (12,2,2.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (13,3,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (14,3,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (15,3,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (16,3,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (17,3,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (18,3,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (19,4,2.55000,1)
INSERT INTO [dbo].[selection_helper] VALUES (20,4,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (21,4,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (22,4,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (23,4,2.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (24,4,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (25,5,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (26,5,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (27,5,6.06428,1)
INSERT INTO [dbo].[selection_helper] VALUES (28,5,5.99520,1)
INSERT INTO [dbo].[selection_helper] VALUES (29,5,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (30,5,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (31,6,2.63000,2)
INSERT INTO [dbo].[selection_helper] VALUES (32,6,4.00481,1)
INSERT INTO [dbo].[selection_helper] VALUES (33,6,2.59000,1)
INSERT INTO [dbo].[selection_helper] VALUES (34,6,4.00481,1)
INSERT INTO [dbo].[selection_helper] VALUES (35,6,3.95000,2)
INSERT INTO [dbo].[selection_helper] VALUES (36,6,4.00000,2)
INSERT INTO [dbo].[selection_helper] VALUES (37,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (38,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (39,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (40,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (41,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (42,7,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (43,8,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (44,8,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (45,8,2.48000,1)
INSERT INTO [dbo].[selection_helper] VALUES (46,8,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (47,8,2.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (48,8,2.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (49,9,2.65000,1)
INSERT INTO [dbo].[selection_helper] VALUES (50,9,2.65000,1)
INSERT INTO [dbo].[selection_helper] VALUES (51,9,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (52,9,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (53,9,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (54,9,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (55,10,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (56,10,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (57,10,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (58,10,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (59,10,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (60,10,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (61,11,3.20410,1)
INSERT INTO [dbo].[selection_helper] VALUES (62,11,3.31000,1)
INSERT INTO [dbo].[selection_helper] VALUES (63,11,2.40000,1)
INSERT INTO [dbo].[selection_helper] VALUES (64,11,2.00000,1)
INSERT INTO [dbo].[selection_helper] VALUES (65,11,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (66,11,2.37000,1)
INSERT INTO [dbo].[selection_helper] VALUES (67,12,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (68,12,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (69,12,2.50000,1)
INSERT INTO [dbo].[selection_helper] VALUES (70,12,2.70000,1)
INSERT INTO [dbo].[selection_helper] VALUES (71,12,2.99000,2)
INSERT INTO [dbo].[selection_helper] VALUES (72,12,3.20000,2)
INSERT INTO [dbo].[selection_helper] VALUES (73,13,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (74,13,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (75,13,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (76,13,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (77,13,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (78,13,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (79,14,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (80,14,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (81,14,2.89771,1)
INSERT INTO [dbo].[selection_helper] VALUES (82,14,2.90000,1)
INSERT INTO [dbo].[selection_helper] VALUES (83,14,2.99000,1)
INSERT INTO [dbo].[selection_helper] VALUES (84,14,3.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (85,15,2.90276,1)
INSERT INTO [dbo].[selection_helper] VALUES (86,15,2.90000,1)
INSERT INTO [dbo].[selection_helper] VALUES (87,15,1.90000,2)
INSERT INTO [dbo].[selection_helper] VALUES (88,15,2.49000,1)
INSERT INTO [dbo].[selection_helper] VALUES (89,15,2.19000,1)
INSERT INTO [dbo].[selection_helper] VALUES (90,15,2.00000,1)
INSERT INTO [dbo].[selection_helper] VALUES (91,16,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (92,16,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (93,16,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (94,16,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (95,16,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (96,16,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (97,17,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (98,17,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (99,17,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (100,17,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (101,17,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (102,17,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (103,18,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (104,18,2.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (105,18,3.30000,1)
INSERT INTO [dbo].[selection_helper] VALUES (106,18,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (107,18,2.30000,1)
INSERT INTO [dbo].[selection_helper] VALUES (108,18,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (109,19,2.11000,1)
INSERT INTO [dbo].[selection_helper] VALUES (110,19,2.50000,1)
INSERT INTO [dbo].[selection_helper] VALUES (111,19,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (112,19,2.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (113,19,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (114,19,2.25000,1)
INSERT INTO [dbo].[selection_helper] VALUES (115,20,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (116,20,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (117,20,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (118,20,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (119,20,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (120,20,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (121,21,3.88000,1)
INSERT INTO [dbo].[selection_helper] VALUES (122,21,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (123,21,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (124,21,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (125,21,3.88000,1)
INSERT INTO [dbo].[selection_helper] VALUES (126,21,3.88000,1)
INSERT INTO [dbo].[selection_helper] VALUES (127,22,2.59000,1)
INSERT INTO [dbo].[selection_helper] VALUES (128,22,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (129,22,2.59000,1)
INSERT INTO [dbo].[selection_helper] VALUES (130,22,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (131,22,2.59000,1)
INSERT INTO [dbo].[selection_helper] VALUES (132,22,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (133,23,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (134,23,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (135,23,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (136,23,2.31000,1)
INSERT INTO [dbo].[selection_helper] VALUES (137,23,2.35000,1)
INSERT INTO [dbo].[selection_helper] VALUES (138,23,2.31000,1)
INSERT INTO [dbo].[selection_helper] VALUES (139,24,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (140,24,2.51000,1)
INSERT INTO [dbo].[selection_helper] VALUES (141,24,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (142,24,2.50000,1)
INSERT INTO [dbo].[selection_helper] VALUES (143,24,2.50000,1)
INSERT INTO [dbo].[selection_helper] VALUES (144,24,2.50000,1)
INSERT INTO [dbo].[selection_helper] VALUES (145,25,2.55000,1)
INSERT INTO [dbo].[selection_helper] VALUES (146,25,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (147,25,2.52000,1)
INSERT INTO [dbo].[selection_helper] VALUES (148,25,2.54000,1)
INSERT INTO [dbo].[selection_helper] VALUES (149,25,2.52000,1)
INSERT INTO [dbo].[selection_helper] VALUES (150,25,2.53000,1)
INSERT INTO [dbo].[selection_helper] VALUES (151,26,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (152,26,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (153,26,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (154,26,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (155,26,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (156,26,1.60000,1)
INSERT INTO [dbo].[selection_helper] VALUES (157,27,3.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (158,27,3.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (159,27,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (160,27,3.20000,1)
INSERT INTO [dbo].[selection_helper] VALUES (161,27,1.60000,2)
INSERT INTO [dbo].[selection_helper] VALUES (162,27,1.60000,2)
CREATE TABLE [dbo].[result_type](
[result_id] [int] NOT NULL,
[result_desc] [varchar] (10) NOT NULL
)
INSERT INTO [dbo].[result_type] VALUES (1, 'Won')
INSERT INTO [dbo].[result_type] VALUES (2, 'Lost')
INSERT INTO [dbo].[result_type] VALUES (3, 'Void')
SELECT * FROM [dbo].[result_type]
SELECT * FROM [dbo].[selection_helper]
SELECT * FROM [dbo].[bet_helper]
DROP TABLE [dbo].[bet_helper]
DROP TABLE [dbo].[selection_helper]
DROP TABLE [dbo].[result_type]