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

Share and Enjoy

 

Possibly Related Posts

No related posts.

 

mssql group_concat (48), group_concat mssql (22), group_concat in mssql (7), mssql group concat (4), group concat mssql (3), group_concat for mssql (3), group concat ms sql (2), ms sql group_concat (2), group_concat sql (2), group_concat ms sql (2), mssql string concat group by (1), mssql group_concat function (1), mysql group_concat as varchar (1), mssql group by group_concat (1), msqsql group conccat#hl=fr (1), ms-sql group concat (1), microsoft group_concat (1), grou_concat tsql (1), group_concat() ms (1), group_ concat t-sql (1), tsql group_concat (1)

Candidates for 70-528 work in a group in a smaller or larger development environment that employs Microsoft Visual Studio .NET 2003 Microsoft Visual Studio 2005. Project management professional, also identified as PMI-001 test, is a significant part of PMI qualification. 650-393 enables you to operate windows vista files recovery software and restore damaged data. 70-284 credential is brought by the Microsoft for the candidates who wish to get proficiency in the Microsoft product.