SQL to combine values of rows in specific Columns

[Charles Middlebrooks]

Ignoring the chaos of our data thats lead me here, I am trying to find a way to combine all values in a row from specific columns into a new colum.

In the below picture the red columns are really the same category (but named poorly). Same situation with the blue columns. I would like to use table transformer to create a new column that combines the values in each respective color column into a new column titled "Automation Opt In" and "CPE Systems Opt In". 

The ideal result of the new CPE System column would be
EBF: 2
Hotfix: 13
Major: 4
Minor: 15

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Charles Middlebrooks ,

You may use the following SQL structure for the case:

SELECT 'Project',
'Column 1' + 'Column 2' AS 'New Column'
FROM
(SELECT *,
CASE WHEN 'Column 2' IS NULL
THEN 0
ELSE 'Column 2'
END
AS 'Column 2'
FROM T1)

In the internal SELECT we put 0 in the empty cells of our table and in the external SELECT we sum the required columns and give a new name for a combined column.

Hope this helps.

[Charles Middlebrooks]

@Katerina Kovriga _Stiltsoft_ Your answer feels like it will work, but I might be making a mistake as my results just end up as all 0's.

SELECT 'Type of Project',
'Automation 100%' + 'Automation Incomplete' + 'Automation Opt In' AS 'Automation Opt-In'
FROM
(SELECT *,
CASE WHEN 'Automation 100%' IS NULL
THEN 0
ELSE 'Automation 100%'
END
AS 'Automation 100%',
CASE WHEN 'Automation Incomplete' IS NULL
THEN 0
ELSE 'Automation Incomplete'
END
AS 'Automation Incomplete',
CASE WHEN 'Automation Opt In' IS NULL
THEN 0
ELSE 'Automation Opt In'
END
AS 'Automation Opt In'
FROM T1)

[Stiltsoft support]

Hi @Charles Middlebrooks ,

Thank you for reaching out!

Seems that your columns' names may be wrong: as we see, your source table is a pivot table, so please use the T1. prefix and autocomplete in the internal SELECT. You might have lost the "Count" part (I mean smth like this: not 'Automation 100%' but 'Count Automation 100%' is the right column name),

For example, you may leave only the internal SELECT in your query and make sure that you get the same big table as a result but with "0" instead of empty cells.

[Clara Vega _Simpleasyty_]

Hi @Charles Middlebrooks

Could I suggest to use Simple Table instead of a longer SQL transformation?

If your data is already in a native Confluence table, you can wrap that table with the Simple Table bodied macro and then add a calculated column that combines the values row by row. Simple Tables supports both wrapping native Confluence tables and creating calculated columns.

For example, I would first rename the source columns to something shorter like:

• auto100

• autoIncomplete

• autoOptIn

Then create a new calculated column called Automation Opt-In with a formula like:

=auto100 + autoIncomplete + autoOptIn

Simple Tables supports renaming headings and arithmetic formulas in calculated columns, so this is usually much easier to read and maintain than a more complex SQL query.

With your example, the result would be:

• EBF = 5

• Hotfix = 15

• Major = 3

• Minor = 4

After that, you can hide the original columns and keep only the final combined column visible. If helpful, you can also add a footer aggregation to show the total sum at the bottom.

So yes, for this use case, Simple Tables could be a much more straightforward solution than building the logic in SQL.

Hope this helps!