SQLTeam.com | Weblogs | Forums

Populating a table based on another table conditionally

tsql
sql2008

#1

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]

#2

This appears to to produce the results you're looking for...

WITH 
	cte_AddRN AS (
		SELECT 
			sh.betNo, 
			sh.bet_id, 
			sh.odds, 
			sh.result_id,
			rn = ROW_NUMBER() OVER (PARTITION BY sh.bet_id ORDER BY sh.betNo)
		FROM
			#selection_helper sh
		)
SELECT 
	ar.bet_id,
	odds1  = MAX(CASE WHEN ar.rn = 1 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 1  THEN 0 END),
	odds2  = MAX(CASE WHEN ar.rn = 2 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 2  THEN 0 END),
	odds3  = MAX(CASE WHEN ar.rn = 3 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 3  THEN 0 END),
	odds4  = MAX(CASE WHEN ar.rn = 4 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 4  THEN 0 END),
	odds5  = MAX(CASE WHEN ar.rn = 5 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 5  THEN 0 END),
	odds6  = MAX(CASE WHEN ar.rn = 6 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 6  THEN 0 END),
	odds7  = MAX(CASE WHEN ar.rn = 7 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 7  THEN 0 END),
	odds8  = MAX(CASE WHEN ar.rn = 8 AND ar.result_id = 1 THEN ar.odds WHEN ar.rn = 8  THEN 0 END)
FROM
	cte_AddRN ar
GROUP BY 
	ar.bet_id;