I have mapped my values using custom mapping. But original values is showing in the column while I am querying the table. I have seen people facing this issue before. is it only applicable with GUI Questions or am I missing something?
If you're using Table Metadata to "remap" column values yes - that's GUI only. Metabase cannot parse the native SQL that you write and inject remapped values into your results.
Is there a way I can achieve custom mapping in sql querying without creating table view on DB. I want to show mapped values inside both column and Dropdown filter.
You could convert the SQL query to a model. Then, in model metadata, you explicitly link the column from the query to the column in your database. By doing this you're telling Metabase which columns in the results map to columns you've defined in "Table Metadata" so the remapped values would display in the model results. Any questions built on top of the models should also bring in the remapped fields (and dashboard filters linked to them would as well).
Unless you convert it into a model and explicitly tell Metabase which column in the result set maps to which column in the DB it cannot get the remapped values - so there's no way to retain the question as a SQL question.