MySql | Show only columns as rows which have specific value

Sometimes, we want to show only those columns which have specific value. Let’s assume we have following table :

In the above table, value of 1 in a column represents that corresponding service is provided by organization and 0 represents service is not provided by the organization.

In the above table, we have shown few columns and very few entries have value 1. In reality, there may exist more columns. In that case, we do not want to fetch the whole row for all organizations. Suppose, we want to fetch just services offered by different organizations and we want to show column names as rows, that is, in the following format :

To get the output as shown in table 2, we need to write mysql query as follows :

SELECT organization, service FROM
(SELECT organization, service, CASE t.service
WHEN ‘software_development’ THEN software_development
WHEN ‘insurance’ THEN insurance
WHEN ‘training’ THEN training
WHEN ‘server_hosting’ THEN server_hosting
WHEN ‘marketing’ THEN marketing
END AS val
FROM tablename CROSS JOIN (
SELECT software_development AS service
UNION ALL SELECT insurance AS service
UNION ALL SELECT training AS service
UNION ALL SELECT server_hosting AS service
UNION ALL SELECT marketing
) t
) t
WHERE val = 1;

Another example is to show only non-working days of the organization. In this example, we have following data :

From Table 3, we can observe that 1 represents working day and 0 represents non-working day and there are fewer entries for value 0. So, in this case, it is better to show records in (organization, non-working day) pair as rows instead of the long row (organization, mon, tue, …)

For this, we can create query in similar way :

SELECT organization, non_working FROM
(SELECT organization, non_working, CASE t.non_working
WHEN ‘mon’ THEN mon
WHEN ‘tue’ THEN tue
WHEN ‘wed’ THEN wed
WHEN ‘thu’ THEN thu
WHEN ‘fri’ THEN fri
WHEN ‘sat’ THEN sat
WHEN ‘sun’ THEN sun
END AS val
FROM tablename CROSS JOIN (
SELECT mon AS non_working
UNION ALL SELECT tue AS non_working
UNION ALL SELECT wed AS non_working
UNION ALL SELECT thu AS non_working
UNION ALL SELECT fri AS non_working
UNION ALL SELECT sat AS non_working
UNION ALL SELECT sun AS non_working
) t
) t
WHERE val = 0;

By executing above query, we will get output in following format :