How to conditionally return multiple different columns in sqlite?

Question

I am trying to return multiple columns only if a condition matches, else, return something else. Using Sqlite3, my understanding is that the way to do this is using CASE, so something like this:

SELECT
CASE 
 WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1) then "Passing" else "Failing" 
END AS Status,
'Encryption Enabled' AS Policy;

This all works great, it returns the following:

Status | Policy
------------------------------
Passing | Encryption Enabled

Now, I am trying to return contextual data if Status = 'Failing'.

For example, if Status = 'Failing', I would like to return the Policy and Status columns, as well as the output of:

SELECT name, type, vault_status FROM disk_encryption;

How would I do this?

Here is simplified test data I am working with:

CREATE TABLE disk_encryption(`name` TEXT, `type` TEXT, `user_uuid` TEXT, `vault_status` TEXT, PRIMARY KEY (`name`)) WITHOUT ROWID;
INSERT INTO disk_encryption
VALUES ('/dev/disk1s1', 'APFS Encryption', '504', 'on'); 

Answer 1

The number of colums in the result cannot be altered within the query depending on the results of the query. What you can do is adding a third column (eg. Info) which will be empty when status='Passing' and populated when status='Failing'.

Content of the Info column can be a concatenation of values from the subquery.

SELECT
CASE 
 WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1) then "Passing" else "Failing" 
END AS Status,
'Encryption Enabled' AS Policy,
CASE 
 WHEN EXISTS(SELECT 1 FROM disk_encryption WHERE user_uuid IS NOT "" AND vault_status = 'on' LIMIT 1)
 THEN ''
 ELSE (SELECT name ||'-'|| type ||'-'|| vault_status FROM disk_encryption)
END AS Info;
Status |Policy |Info
-----------------------------------------------------------
Failing|Encryption Enabled|/dev/disk1s2-APFS Encryption-off

• Josh Brower: This is great! I was playing around with CTEs, but this is much simpler.