sql - Selecting Min/Max from Comma Separated Values against each record -

consider below table , records

create table dbo.test ( id  numeric(4), vals    nvarchar(1000) );  insert dbo.test values (1,'1,2,3,4,5'); insert dbo.test values (2,'6,7,8,9,0'); insert dbo.test values (3,'11,54,76,23'); 

i going use below function split csvs, can use method in select syntax

create function [aml].[split](@string varchar(8000), @delimiter char(1))      returns @temptable table (items varchar(8000))           begin      declare @idx int      declare @slice varchar(8000)       select @idx = 1          if len(@string)<1 or @string 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 @temptable(items) values(@slice)           set @string = right(@string,len(@string) - @idx)          if len(@string) = 0 break      end  return      end 

i want select id , max , min values vals against each record.

update though writing query on sql server 2008 need support sql server 2005 , above

you can cross apply table projected function , apply normal aggregation functions on each group of id :

select t.id, min(cast(x.items int)) minitem, max(cast(x.items int)) maxitem dbo.test t       cross apply dbo.split(t.vals, ',') x group t.id; 

(edit - since these appear integers, you'll want cast before applying min / max aggregates otherwise you'll alphanumeric sort)

sqlfiddle example here

another option persist comma separated list in normalized table structure before applying queries on them - isn't useful storing non-normalized data in rdbms :)