Selecting Min/Max from Comma Separated Values against each record

Question

consider below table and its records

create table dbo.test
(
id  numeric(4),
vals    nvarchar(1000)
);

insert into dbo.test values (1,'1,2,3,4,5');
insert into dbo.test values (2,'6,7,8,9,0');
insert into dbo.test values (3,'11,54,76,23');

I am going to use below function to split CSVs, you can use any method to help in select syntax

CREATE FUNCTION [aml].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

I want to select id and max and min values from vals against each record.

Update Though i am writing the query on SQL Server 2008 but i need to support SQL Server 2005 and above

Answer 1

You can CROSS APPLY to the table projected by the function and then apply normal aggregation functions on each group of id :

SELECT t.id, MIN(CAST(x.items AS INT)) AS MinItem, MAX(CAST(x.items AS INT)) AS MaxItem
FROM dbo.test t 
     CROSS APPLY dbo.Split(t.vals, ',') x
GROUP BY t.id;

(Edit - since these appear to be integers, you'll want to cast before applying the MIN / MAX aggregates otherwise you'll get an alphanumeric sort)

SqlFiddle example here

Another option is to persist the comma separated list in a normalized table structure before applying queries over them - it isn't useful storing non-normalized data in an RDBMS :)

Answer 2

Without function, just plain sql:

SELECT t.id,  
       Max(Split.a.value('.', 'VARCHAR(100)')) AS MaxVal,
       Min(Split.a.value('.', 'VARCHAR(100)')) AS MinVal
 FROM
 (
     SELECT id,  
         CAST ('<M>' + REPLACE(vals, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  test
 ) AS t CROSS APPLY Data.nodes ('/M') AS Split(a)
 group by t.id

Fiddle http://sqlfiddle.com/#!3/22321/6