Help me please! There are 2 tables:
CREATE TABLE Options ( id_opt smallint NOT NULL PRIMARY KEY, [name_opt] varchar(10) ); CREATE TABLE Items ( id_item int NOT NULL, id_opt smallint FOREIGN KEY REFERENCES Options(id_opt), some_date smalldatetime, [some_val] int ); It is necessary to build a table of the form
----------name0 Name1 … nameN id_item0 Value Value … Value id_item1 Value Value … Value Filling data:
INSERT INTO params VALUES (1, 'param_1'); INSERT INTO Options VALUES (2, 'param_2'); INSERT INTO Options VALUES (3, 'param_3'); INSERT INTO Options VALUES (4, 'param_4'); INSERT INTO Options VALUES (5, 'param_5'); INSERT INTO Items VALUES (1, 1, '1980-11-1 11:15:10', 5); INSERT INTO Items VALUES (1, 1, '1980-9-1 11:15:20', 15); INSERT INTO Items VALUES (1, 1, '1981-8-5 11:15:30', 234); INSERT INTO Items VALUES (1, 1, '1980-10-1 11:15:40', 654); INSERT INTO Items VALUES (1, 2, '1980-10-1 11:15:10', 7); INSERT INTO Items VALUES (1, 3, '1980-10-1 11:15:10', 854); INSERT INTO Items VALUES (2, 1, '1980-10-1 11:15:10', 75); INSERT INTO Items VALUES (2, 3, '1980-10-1 11:15:10', 84); INSERT INTO Items VALUES (2, 5, '1980-10-1 11:15:10', 33); INSERT INTO Items VALUES (3, 1, '1980-10-1 11:15:10', 35); INSERT INTO Items VALUES (3, 2, '1980-10-1 11:15:10', 22); INSERT INTO Items VALUES (3, 3, '1980-10-1 11:15:10', 15); INSERT INTO Items VALUES (3, 4, '1980-10-1 11:15:10', 45); INSERT INTO Items VALUES (3, 5, '1980-10-1 11:15:10', 564); I wrote:
SELECT id_item, param_1 = ISNULL((SELECT [some_val] FROM Items WHERE [name_opt] = 'param_1' AND id_item = sel.id_item AND some_date = '1980-10-1 11:15:10'),0), param_2 = ISNULL((SELECT [some_val] FROM Items WHERE [name_opt] = 'param_2' AND id_item = sel.id_item AND some_date = '1980-10-1 11:15:10'),0), param_3 = ISNULL((SELECT [some_val] FROM Items WHERE [name_opt] = 'param_3' AND id_item = sel.id_item AND some_date = '1980-10-1 11:15:10'),0), param_4= ISNULL((SELECT [some_val] FROM Items WHERE [name_opt] = 'param_4' AND id_item = sel.id_item AND some_date = '1980-10-1 11:15:10'),0), param_5= ISNULL((SELECT [some_val] FROM Items WHERE [name_opt] = 'param_5' AND id_item = sel.id_item AND some_date = '1980-10-1 11:15:10'),0) FROM (select id_item, [name_opt], [some_val] from Items join Options ON (Items.id_opt = Options.id_opt)) sel GROUP BY id_itemt It gives the correct result, but this is not a dynamic structure, if a new option is added in Options, you will need to add "param_6 = ...". Dynamics can be done through the cursor, but I do not understand how. In internal selects, where "some_date = '1980-10-1 11:15:10'" is a temporary implementation, it will be necessary to organize this into a function with a date argument, so that the same table will be returned by date. But for the time being, I would at least just build a dynamic query that I cannot write.
