SQL Comma Delimted Column => to Rows Then Sum Totals

SQL comma delimted column = to rows then sum totals?

I prefer the number table approach to split a string in TSQL

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can now use a CROSS APPLY to split every row in your table like:

DECLARE @YourTable table (NameList varchar(5000), TimeOf int)
INSERT INTO @YourTable VALUES ('John Smith, Jeremy Boyle, Robert Brits, George Aldrich',  5)
INSERT INTO @YourTable VALUES ('John Smith, Peter Hanson',    15)
INSERT INTO @YourTable VALUES ('Jeremy Boyle, Robert Brits',  10)

SELECT
    st.ListValue AS NameOf, SUM(o.TimeOf) AS TimeOf
    FROM @YourTable  o
        CROSS APPLY  dbo.FN_ListToTable(',',o.NameList) AS st
    GROUP BY st.ListValue
    ORDER BY st.ListValue

OUTPUT:

NameOf                  TimeOf     
----------------------- -----------
George Aldrich          5          
Jeremy Boyle            15         
John Smith              20         
Peter Hanson            15         
Robert Brits            15         

(5 row(s) affected)

Using this, I would recommend that you alter your table design and use this output to INSERT into a new table. That would be a more normalized approach. Also Don't use reserved words for column names, it makes it a hassle. Notice how I use "NameOf" and "TimeOf", so I avoid using reserved words.

Comma-separated values to rows with sum in SQL Server

Sub-query is really not necessary :

select stuff((select ','+t1.org from #t t1 for xml path('')), 1, 1, '') as org, 
       sum(area) as area
from #t t;

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

Count or Sum values in a column separated by a comma in Oracle SQL

Please try:

SET SERVEROUTPUT ON
DECLARE
  lv_formula VARCHAR2(1000):='0,0,1,1,1,2';
  result NUMBER;
BEGIN
    lv_formula:=replace(lv_formula, ',', '+');
    EXECUTE IMMEDIATE 'BEGIN :out := '||lv_formula||'; END;' USING OUT result;
    DBMS_OUTPUT.PUT_LINE('Answer is...' || result);
END;

Adding comma seperated column values in a table

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:

RESULTS:

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:

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.

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

---

Related Topics