Use Table Transformer to select columns if existing

[Eva Kröger]

Hi there! 

I want to transform a table and select three columns but one of the columns might not exist in some cases. I am using this command: 

SELECT 'Sprint',
'Sum of Story-points a' AS 'Work added',
'Sum of Story-points x' AS 'Work completed',
'Sum of Story-points ‎' AS 'Work remaining'
FROM T*

I thought I could adjust it to something like this:

SELECT 'Sprint',
if exists select t1.'Sum of Story-points a' AS 'Work added',
'Sum of Story-points x' AS 'Work completed',
'Sum of Story-points ‎' AS 'Work remaining'
FROM T*

But it's not working. What could I do here?

[Stiltsoft support · Accepted solution]

Hi @Eva Kröger ,

You may use two options for the case:

SELECT
'Text 1' AS 'New 1',
'Text 3' AS 'New 3',
COALESCE('Text 2',"") AS 'New 2'
FROM T*

or

SELECT
'Text 1' AS 'New 1',
'Text 3' AS 'New 3',
IFNULL('Text 2') AS 'New 2'
FROM T*

Here the 'Text 1' and 'Text 3' columns are always present. The 'Text 2' column is optional (it may be present or not).

If the 'Text 2' column exists, then you'll get its real contents. If it is not present in the source table, then you'll get an empty column in the result table and no errors from the Table Transformer macro.

Hope it helps.

[Eva Kröger]

Thanks a lot, that helped!