CODEWITHSUNDEEP
sqlmultiple-columnsaddition
Deepa Das
Deepa Das

Reputation: 43

Adding comma seperated column values in a table

I have 2 columns in a table.

|  values_1 | values 2 |

| 1,2,3,4   | 5,6,7,8  |

I need to add both column values..i.e, the result should be 6,8,10,12

Upvotes: 0

Views: 1294

Answers (2)

ceving
ceving

Reputation: 23824

In PostgreSQL you can split a string into a table (regexp_split_to_table). After that you can paste the tables into columns of your source table (from rows from). You have to keep the right order (with ordinality). Then you can add the two values after converting them to integers (::int). In order to convert the summed values back into an array, you have to group them by the original values (group by). Then you can aggregate the summed values into an array (array_agg) while keeping the original order (order by ordinality). Next you can format the array as string (array_to_string).

Example (SQL Fiddle):

-- table
create table "values"
(
  "values 1" text,
  "values 2" text
);

-- data
insert into "values" values ('1,2,3,4', '5,6,7,8');
insert into "values" values ('11,22,33,44', '55,66,77,88');

-- select
select
  "values 1", "values 2",
  array_to_string(array_agg (s order by ordinality), ',') "values 1+2"
from
  (select
     "values 1", "values 2", ordinality,
     v1::int + v2::int s
   from
     "values",
     rows from
     (regexp_split_to_table ("values 1", ','),
      regexp_split_to_table ("values 2", ','))
     with ordinality alias (v1, v2)) tmp
group by "values 1", "values 2";

Don't expect this to be fast.

Upvotes: 0

Visionstar
Visionstar

Reputation: 365

You obviously should not use such a data model, because it will bring a lot of trouble with it. But if you have no other choice then you need to find at least some solution.

With MSSQL, you can perform the following query to get the result you desire:

SELECT a.id, a.values_1, a.values_2, v1.rowindex_left, v2.rowindex_right, v1.value as lefty, v2.value as righty, CAST(v1.value AS INT)+CAST(v2.value AS INT) as result 
    FROM testtable a
        CROSS APPLY (select value, ROW_NUMBER() OVER(ORDER BY value ASC) AS rowindex_left from string_split(values_1, ',')) as v1
        CROSS APPLY (select value, ROW_NUMBER() OVER(ORDER BY value ASC) AS rowindex_right from string_split(values_2, ',')) as v2
        where v1.rowindex_left = v2.rowindex_right

BASE:

enter image description here

RESULTS: enter image description here

What it does:

  • it transforms your data (by using string_split), so that you can use each value for further calculation
  • SELECT-clause: the values of v1.value and v2.value are casted from varchar(x) to a INT and are added together
  • WHERE-clause: only display the data, where the rownumber is matching (to represent the desired calculation)

You always need to prepare your data first, so that you then can use the seperate values for your calculation.

Now, we can perform the following Statement, to put the seperated values together:

SELECT id, STRING_AGG(result, ',') from (
    SELECT a.id, a.values_1, a.values_2, v1.rowindex_left, v2.rowindex_right, v1.value as lefty, v2.value as righty, CAST(v1.value AS INT)+CAST(v2.value AS INT) as result 
    FROM testtable a
        CROSS APPLY (select value, ROW_NUMBER() OVER(ORDER BY value ASC) AS rowindex_left from string_split(values_1, ',')) as v1
        CROSS APPLY (select value, ROW_NUMBER() OVER(ORDER BY value ASC) AS rowindex_right from string_split(values_2, ',')) as v2
        where v1.rowindex_left = v2.rowindex_right 
    ) t group by id

Afterwards, you should get the following result:

enter image description here

If you have a lot of data, you could insert the seperate values from v1+v2 into another table and then perform the needed calculations - this would boost the performance a lot, if you need to perform calculations on this dataset many times.

Upvotes: 1

Related Questions