2006
01.10

MS SQL version of MySQL’s Group_Concat

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
GD Star Rating
loading...

Incoming Search Term

mssql group_concat, group_concat mssql

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.

1 comment so far

Add Your Comment
  1. Thank you so much. That did exactly what I needed it to do.