Threshold SQL Table

Question

I have a screenshot of a GUI and need to make it into a SQL table. What would be the best way of going about this?

Answer 1

There are 3 distinct entities in that screenshot, so define 3 tables.

Create Table ProbabilityThresholds(
  Name varchar (50),
  MinValue int,
  MaxValue int
)

Create Table RisksMatrix (
  Probability varchar(50),
  MinorSeverity varchar(50),
  ModerateSeverity varchar(50),
  MajorSeverity varchar(50),
  ExtremeSeverity varchar(50),
  SortOrder int
 )

Create Table RiskToCategory (
  Risk varchar(50),
  Category varchar(50),
  SortOrder int
)

Then Query the 3 tables in multiple result sets (either as 3 direct queries or as a stored procedure). The as expression sets a column alias, and the SortOrder column is populated to sort in the desired layout, but is not selected in the results:

Select Name
, MinValue
, MaxValue 
from ProbabilityThresholds 
order by MinValue;

Select  
      Probability 
      ,MinorSeverity as "Minor Harm",
      ,ModerateSeverity as "Moderate Harm",
      ,MajorSeverity as "Major Harm",
      ,ExtremeSeverity as "Extreme Harm",
from RisksMatrix  
order by SortOrder ;

Select 
    Risk
   ,Category 
from RiskToCategory 
order by SortOrder;