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.