Aggregate Field Type String 1

[sipos]

Hi,
these days i'm trying to build a group-by-SQL-Query to aggregate a string

This is an example of dataset i have
Dept Name Jenis Qty
=======================
A 1 X 2
A 2 Y 3
B 1 X 1
C 3 X 2
C 1 X 1

now, is there a group-by-SQL-Query to aggregate that dataset into below data model?
Dept Name Jenis Total Qty
=========================
A 1;2 X+Y 5
B 1 X 1
C 3;1 X+X 3

Creating aggregate on Qty is as simple as statement SUM(), but aggregate-a-string-field is not a simple answer. Is that true that I should use cursor to create that?

 

[RyanEK]

Hi,

Here's a way you can do it using xml path for:

select Dept,
replace((
     select Name as 'data()'
	 from [Table] t2 
     where t1.Dept = t2.Dept
	 for xml path('')),' ',';') as Name,
replace((
     select Jenis as 'data()'
	 from #tmp t2 
     where t1.Dept = t2.Dept
	 for xml path('')),' ',';') as Jenis,
sum (qty)
from [Table] t1
group by Dept

Ryan

 

[RyanEK]

Sorry that should be

select Dept,
replace((
     select Name as 'data()'
     from [Table] t2 
     where t1.Dept = t2.Dept
     for xml path('')),' ',';') as Name,
replace((
     select Jenis as 'data()'
     from [Table] t2 
     where t1.Dept = t2.Dept
     for xml path('')),' ',';') as Jenis,
sum (qty)
from [Table] t1
group by Dept

 

[gmmastros]

For those reading this....

Ryan's method works well for SQL 2005, but will not work on SQL 2000.

I don't say this to discredit the method. Far from it. I just don't want there to be any confusion.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lalitha1]

I agree Xml path works in SQL 2005 but not in SQL 2000.
I have the same problem.

 

[gmmastros]

For SQL2000, you may find this useful:

thread183-1159740


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sipos]

Hi, ryanEk
thanks for ss 2k5's code, but yes, i will use the code on ss 2k, so your code will not work. But ... again ... many thanks

and to gmmastros
thanks for the links, it helps a lot.

 

[firecat82]

Hi RyanEK,

I had similar problem and your method is very helpful. But if column I want to aggregate contains values with spaces, it works so: 'Name 1' and 'Name 2' are aggregated to 'Name;1;Name;2' is it possible to aggregate such values to 'Name 1;Name 2'?
Thank you