SQL Query for this requirement

Question

I have a sqlite DB with tables sample ,surveytype and status .All 3 tables primary key id is set as foreignkey to table 'Operation".Please see the scema in the following image

My operation table has hence values

and sample as

and status

and survey type as

REQUIRED

What i need to achieve is

From the operation table get information of status id and show it for specific buildings for all survey types.

Tried

I found a way by which to loop via program getting all the sample ids and all survey typeids in and get individual statusids as

for all sampleid in sample table
{
   for all statustypeid in statustype table
   {  
      select statusid from operation where sampleid = 2 and surveytypeid=1
    }
}

Is there a better option by sql single query to generate the table with all the values

Note : Using sqlite,for ios app

Answer 1

First of all: why is Operation.StatusID of type Text, when it's filled with values from integer field? The same may be asked about RegionID, but as Region table is not listed here I can't tell, maybe you need Text in that particular case. But having Operation.StatusID as Text is totally unjustified and will give you performance penalty.

Nevertheless, there is an old trick, called pivoting but it will work only for a fixed number of columns, in your case - Surveys:

SELECT
  Sample.BuildingID,
  MAX(Sample.BuildingName_EN) BuildingID,
  MAX(CASE WHEN Operation.SurveyTypeID=1 THEN Operation.StatusID ELSE " " END) AS Survey1,
  MAX(CASE WHEN Operation.SurveyTypeID=2 THEN Operation.StatusID ELSE " " END) AS Survey2,
  MAX(CASE WHEN Operation.SurveyTypeID=3 THEN Operation.StatusID ELSE " " END) AS Survey3
FROM Sample,Operation
WHERE Operation.SampleID=Sample.ID
GROUP BY Sample.BuildingID

Update: To ensure expandability of this solution, you will have to generate this query each time before use with another one:

SELECT '  SELECT Sample.BuildingID,MAX(Sample.BuildingName_EN) BuildingID,'
UNION ALL
SELECT concat(' MAX(CASE WHEN Operation.SurveyTypeID=',SurveyType.ID,' THEN Operation.StatusID ELSE " " END) AS "',SurveyType.LongName,'",') from SurveyType
UNION ALL
SELECT '0 as dummy FROM Sample,Operation WHERE Operation.SampleID=Sample.ID GROUP BY Sample.BuildingID'

Or, for Oracle and SQLite:

SELECT '  SELECT Sample.BuildingID,MAX(Sample.BuildingName_EN) BuildingID,'
UNION ALL
SELECT ' MAX(CASE WHEN Operation.SurveyTypeID='||SurveyType.I||' THEN Operation.StatusID ELSE " " END) AS "'||SurveyType.LongName||'",' from SurveyType
UNION ALL
SELECT '0 as dummy FROM Sample,Operation WHERE Operation.SampleID=Sample.ID GROUP BY Sample.BuildingID'