Group By question, please help

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ray (anonymous_at_discussions.microsoft.com)
Date: 04/01/04 

Date: Thu, 1 Apr 2004 07:16:13 -0800

Hi all,

I have difficulty to figure this out and need your help. I have provided the following script to depict my issue:
Create table test(
value0 int identity(1,1),
value1 char(1),
value2 char(1),
value3 char(1),
value4 char(1),
value5 char(2)
)
insert into test(value1, value2, value3, value4, value5)
values('a','b','c','d','z1')
insert into test(value1, value2, value3, value4, value5)
values('a','b','c','d','z2')
insert into test(value1, value2, value3, value4, value5)
values('a','b','c','d','z3')
insert into test(value1, value2, value3, value4, value5)
values('a','b','c','d','z4')

I like to see the resultset as
a b c d z1z2z3z4

if data type of value5 was numeric then I coudl write:

select value1, value2, value3, value4, sum(value5) from test
group by value1, value2, value3, value4, value5

Thanks in advance,
Ray