mercredi 3 juin 2015

Selecting Min/Max from Comma Separated Values against each record

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.

Aucun commentaire:

Enregistrer un commentaire