I am using the query below which gives me all reports and its Excel Format ID for example 1=Excel 2007, 2=XLSX, 3=CSV
SELECT Reportname AS 'ReportName', Excelformatid AS 'Excel Format' FROM reports;
Is it possible to get the actual format instead of the ID, in the output?
I went through the below link, but it tells me how I can change the output considering only a specific column value, and not all.
'IF' in 'SELECT' statement - choose output value based on column values
The easiest way would be to make a lookup table and do a join, but if you don't have that available a CASE will do the trick.
SELECT
Reportname AS 'ReportName',
CASE Excelformatid
WHEN 1 THEN 'Excel 2007'
WHEN 2 THEN 'XLSX'
WHEN 3 THEN 'CSV'
ELSE 'Unknown'
END AS 'Excel Format'
FROM reports;
It would be easy to implement a lookup table (let's call it 'formats') with data like this:
+---------------+------------+
| Excelformatid | name |
+---------------+------------+
| 1 | Excel 2007 |
| 2 | XLSX |
| 3 | CSV |
+---------------+------------+
Then you can use a join like so:
SELECT Reportname, formats.name
FROM reports
LEFT JOIN formats USING(Excelformatid)
You can use a CASE statement
SELECT
Reportname AS 'ReportName',
CASE
WHEN Excelformatid = 1 THEN 'Excel 2007'
WHEN Excelformatid = 2 THEN 'XLSX'
WHEN Excelformatid = 3 THEN 'CSV'
END CASE AS 'Excel Format'
FROM
reports
See here for the documentation
Try:
SELECT
Reportname AS 'ReportName',
CASE
WHEN Excelformatid = '1' THEN 'Excel 2007'
WHEN Excelformatid = '2' THEN 'XLSX'
WHEN Excelformatid = '3' THEN 'CSV'
END AS 'Formate Not Available'
FROM
reports
Also You can make masterformat table and join with reports which give you dynamic result
