How to manage a value from a row to generate a column of data associated to a specific key of the table?

Question

This is my starting table:

Test ID	Key	Value	OrderNumber
1	model	MODEL_NAME1	100000
1	test 1	10.0	100000
1	test 2	100.0	100000
1	test 3	20.0	100000
1	test 4	40.0	100000
2	model	MODEL_NAME2	100002
2	test 1	15.0	100002
2	test 2	89.0	100002
2	test 3	37.0	100002

I would like to add a column on the right of the column "OrderNumber" with the "MODEL_NAME" value to give more clearance to the reader of what type of MODEL is the value corresponding to the table in this way :

Test ID	Key	Value	OrderNumber	ModelName
1	model	MODEL_NAME1	100000	MODEL_NAME1
1	test 1	10.0	100000	MODEL_NAME1
1	test 2	100.0	100000	MODEL_NAME1
1	test 3	20.0	100000	MODEL_NAME1
1	test 4	40.0	100000	MODEL_NAME1
2	model	MODEL_NAME2	100002	MODEL_NAME2
2	test 1	15.0	100002	MODEL_NAME2
2	test 2	89.0	100002	MODEL_NAME2
2	test 3	37.0	100002	MODEL_NAME2

Since the ModelName could be repeated in the table for other product order i know that the solution is to use "Test ID" and "OrderNumber" like externalkey for the right association of each MODEL_NAME in the right row.

I'm new to SQL and i don't know if it is a possible solution. Thanks in Advance.

UPDATE: thanks to @Squirrel and @allmhuran i resolved my problem and learnt new things about EAV and building Querys, i'm so grateful, thanks again guys!

Answer 1

Change your design.

From your data I infer that the orderNumber is determined by the model, although there is insufficient information to know this for sure. I also have to guess the data types for the columns based on the data in your question.

It's also not clear if the different tests ("test 1", "test 2", etc), are different kinds of tests, or just repetitions of the same test. Similarly it's not clear whether the results of those tests are the same kind of results, or different kinds of results. For example, is "test 1" measuring weight, whereas "test 2" is measuring crash tolerance? Is the "result" a percentage of an expected outcome? An absolute measured value? In what units is the result? We don't know.

These are some of, but far from all of, the problems with an EAV.

I think you want a models table to store the model information, and a testResults table to indicate the tests that have been performed against the model. If you have multiple different kinds of tests, each with a different kind of result, you might want multiple different test tables. For now I will assume that each "test" is just the same kind of test run repeatedly for each model, but if that's not true let me know and I will show you a way we might handle that (it's not very complicated)

In order to generate the result you want to achieve in this question, you simply join the tables together:

create table Models
(
   modelName varchar(32) primary key,
   OrderNumber int
);

create table TestResults
(
   modelName varchar(32) foreign key references Models,
   testName varchar(16),   
   testResult decimal(4,1),
   primary key (modelName, testName)
);

insert Models (modelName, orderNumber) values
('MODEL_NAME1', 100000),
('MODEL_NAME2', 100002);

insert TestResults (modelName, testName, testResult) values
('MODEL_NAME1', 'test 1', 10.0 ),
('MODEL_NAME1', 'test 2', 100.0),
('MODEL_NAME1', 'test 3', 20.0 ),
('MODEL_NAME1', 'test 4', 40.0 ),
('MODEL_NAME2', 'test 1', 15.0 ),
('MODEL_NAME2', 'test 2', 89.0 ),
('MODEL_NAME2', 'test 3', 37.0 );


select   m.modelName, m.orderNumber, t.testName, t.testResult
from     Models       m
join     TestResults  t on t.modelName = m.modelName
order by Modelname, testName;

You may notice I have removed the test ID column. If the test name is unique within a model, then the id column wasn't needed.

You might see commentary about "always adding an identity surrogate key to your tables". There was in fact a recent question about that. My thoughts on the topic are here, and you will also see other poeple's opinions, as well as a link to a prior discussion of the same topic.

^{( I'm right, by the way :P )}

You might also see that I have "wide" key columns, which you will hear "makes queries slow". But let's not concern ourselves about performance yet. Let's understand the principles of relational database design before thinking about optimization techniques.

Answer 2

You can use max() with over together with case expression to check for Key = model only

select ModelName = max (case when [Key] = 'model' then [Value] end) 
                     over (partition by [Test ID])
from   . . .