SQL comma divided column => into rows, and then sums?

Question

I am using MS SQL 2005 I have a problem that I am currently struggling for a solution.

I have a table with such columns: NameList; Time

The Namelist column contains comma-separated data. The table data is as follows:

Namelist Time John Smith, Jeremy Boyle, Robert Brits, George Aldrich 5 John Smith, Peter Hanson 15 Jeremy Boyle, Robert Brits 10 .... 

I need some kind of SQL expression that will provide me with this final result:

 Name Total_Time John Smith 20 Jeremy Boyle 15 Robert Brits 15 

Etc ...... Basically, the expression should find all the names in the lines and the mathematical names with the names in the other lines and add time for each user.

The idea I have is to convert comma-delimited data to strings and count the individual entries of each of them, and then somehow find out what time it is for it ... then multiply ..... but I have no idea how to implement it

Any help would be greatly appreciated

Thanks,

Answer 1

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

For this method to work, you need to complete this setup at once:

 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 configured, 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 

Now you can easily split the CSV row into a table and join it:

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

CONCLUSION:

 ListValue ----------------------- 1 2 3 4 5 6777 (6 row(s) affected) 

Now you can use CROSS APPLY to split each row in the table, for example:

 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 

CONCLUSION:

 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 changing the table design and using this output for INSERT in the new table. This will be a more normalized approach. Also, do not use reserved words for column names, this makes work difficult. Notice how I use "NameOf" and "TimeOf", so I avoid using reserved words.

Answer 2

Or: find other answers to correct your data on the fly slowly and repeatedly.

Or: Normalize. Why do you think normalization exists and why do people talk about it?

Answer 3

You can create a table-oriented function to split an element into multiple lines:

 if object_id('dbo.fnSplitNamelist') is not null drop function dbo.fnSplitNamelist go create function dbo.fnSplitNamelist( @namelist varchar(max)) returns @names table ( name varchar(50)) as begin declare @start int declare @end int set @start = 0 while IsNull(@end,0) <> len(@namelist) + 1 begin set @end = charindex(',', @namelist, @start) if @end = 0 set @end = len(@namelist) + 1 insert into @names select ltrim(rtrim( substring(@namelist,@start,@ end-@start ))) set @start = @end + 1 end return end go 

You can use cross apply to return names for each item in a list. Then you can use group by to summarize the time for each user:

 declare @YourTable table (namelist varchar(1000), time int) insert into @YourTable select 'John Smith, Jeremy Boyle, Robert Brits, George Aldrich', 5 union all select 'John Smith, Peter Hanson', 15 union all select 'Jeremy Boyle, Robert Brits', 10 select fn.name, sum(t.time) from @YourTable t cross apply fnSplitNamelist(t.namelist) fn group by fn.name 

It leads to:

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

Answer 4

The best option is to normalize the data. Then it would be much easier to work with them.

The second best option would be to use a recursive query to select a name at a time from each list of names and return as a list of individual names and their response times from each record, then use grouping to summarize the time for each name.

No need for custom functions or pre-created tables .;)

 with NameTime ([Name], [Time], Namelist) as ( select cast(null as varchar(100)), [Time], Namelist from NamelistTime union all select case when Pos = 0 then NameList else substring(Namelist, 1, Pos - 1) end, [Time], case when Pos = 0 then null else substring(NameList, Pos + 2, len(Namelist) - Pos - 1) end from ( select [Time], Namelist, Pos = charindex(', ', Namelist) from NameTime ) x where Namelist is not null ) select [Name], sum([Time]) from NameTime where [Name] is not null group by [Name] 

Unlike working with normalized data, this would be as simple as:

 select p.Name, sum(n.Time) from NamelistTime n inner join Person p on p.PersonId = n.PersonId group by p.Name