Today i’m doing some programming to help Budi catering JSML’s requests. At one point, i need to have an aggregate function for varchar data type. I immediately remember ‘GROUP_CONCAT’, but hey … thats MySQL. Looking through the MS SQL Book Online, couldn’t find any reference about it.

Googling for 5 minutes, i found what i want …

These are the snippets:

create table table1([id] int identity,
names varchar (50),
groupname varchar (50))

insert into table1(Names, GroupName)

select 'name1', 'group1'union
select 'name2', 'group2'union
select 'name3', 'group1'

create function concatname(@GroupName varchar(50))
Returns varchar(250)
as
BEGIN
Declare @String varchar(250)

Select @String = Coalesce(@String,'') + Names + ','
from table1
where groupName = @GroupName

set @String = left(@String, len(@String) -1)
Return @String
END

After trial and error for a couple times, i come up with my
own solution to the problem:

create function SCROverallStatus(@thelog_uid int)
returns varchar(50)
as
begin
 if (select count(*) from LMB_SML_PROC_SCR_APPROVAL where approval_level=1 and status='Approved' and log_uid=@thelog_uid)>=2
 begin
  return 'Approved'
 end

 if (select count(*) from LMB_SML_PROC_SCR_APPROVAL where approval_level=1 and status='Rejected' and log_uid=@thelog_uid)>=1
 begin
  return 'Rejected'
 end

 return 'Pending'
end
Powered by Gregarious (42)
Share This

Hardono Arifanto :: Jan.10.2006 :: Database :: No Comments »