Today i did some programming to help Budi catering JSML’s requests. At one point, I needed to have an aggregate function for varchar data type. I immediately remember ‘GROUP_CONCAT’, but hey … thats MySQL. Skimming through the MS SQL Book Online, I couldn’t find any function that could do what MySQL’s GROUP_CONCAT does.
Like any mere mortals would do, I asked Google. After Googling for 5 minutes, i think I found what i need …
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
loading...
About Hardono
Incoming Search
mssql group_concat, group_concat mssql
Thank you so much. That did exactly what I needed it to do.