1

Developing a database to store test results from different models of a simulation i got this:image

See the "test" column refers to a specific scenario that was tested with one or more "modelo",or model, and "resultado" is the result from that test.

The thing is: i need a single query that for each "test" return the results from each "modelo", that catch is, there is a undefined number of "modelo". The result from such query would be something like:

image2

How can i do this? I've tried with group by without success. Also, if possible, please list the operations used in the query for further learning.

Yan Werneck
  • 31
  • 3

1 Answers1

0

Your problem is pretty similar to the the problem explained in this topic: Simple way to transpose columns and rows in Sql?. Technically, you need to generate a transpose sql query.

(Draft)

SELECT test,
       COALESCE(MAX(CASE WHEN modello = 1 THEN resultado END), null) modello1,
       COALESCE(MAX(CASE WHEN modello = 2 THEN resultado END), null) modello2,
       COALESCE(MAX(CASE WHEN modello = 3 THEN resultado END), null) modello3
 FROM your_table
 GROUP BY test

The problem is that this part

COALESCE(MAX(CASE WHEN modello = 1 THEN resultado END), null) modello1,
COALESCE(MAX(CASE WHEN modello = 2 THEN resultado END), null) modello2,
COALESCE(MAX(CASE WHEN modello = 3 THEN resultado END), null) modello3

Should be generated dynamically if number of modelly values is not really limited. If it is limited, that you can build static query that covers all values of modelly column.

Evgeny Semionov
  • 323
  • 2
  • 9
  • Thanks, guess i could use that to solve my problem by doing the dynamical part in the application, all i need to do is query for distinct(modelo) there and generate one such line for each one of them. Right? – Yan Werneck Aug 24 '17 at 16:43