Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

SQL to combine values of rows in specific Columns

Charles Middlebrooks February 15, 2024

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

Opt In Metrics.JPG

1 answer

1 accepted

3 votes
Answer accepted
Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 16, 2024

Hi @Charles Middlebrooks ,

You may use the following SQL structure for the case:

Fri 5-1.png

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)

Fri 5-2.png

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 February 16, 2024

@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)

Opt in 2.JPG

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 16, 2024

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.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events