nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Opt In Metrics.JPG

Accepted answers

Katerina Kovriga {Stiltsoft}

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.

All comments

Katerina Kovriga {Stiltsoft}

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

@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

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets

pivot-table

unknown-hosting-type