October 24, 2003 at 2:44 am
Here is a table table1 and its content:
Name Type
===============
A 1
A 2
B 2
B 3
C 1
C 2
C 3
How can I get a query result like this:
Name Types
==============
A 1,2
B 2,3
C 1,2,3
Thanks
October 24, 2003 at 8:59 am
Here is an example...but I would take a look at why this is needed and see if perhaps there is not another way to handle this issue.
create table TestMe (
myID varchar(5),
myVal varchar(10) )
insert into TestMe
select 'A', '1'
union select 'A', '2'
union select 'B', '2'
union select 'B', '3'
union select 'C', '1'
union select 'C', '2'
union select 'C', '3'
create function ValList ( @anID VARCHAR(5) ) returns varchar(500) as
BEGIN
DECLARE @retval VARCHAR(500)
SET @retval = ''
SELECT @retVal = @retval + CASE WHEN @retVal = '' THEN '' ELSE ',' END + myVal
FROM TestMe (NOLOCK) WHERE myID = @anID
RETURN @retVal
END
select DISTINCT myID, dbo.ValList( myID )
from TestMe
Guarddata-
October 26, 2003 at 7:26 pm
guarddata,
Thank you for your answer.
Actually, the case is:
We are an IT industry analysis and consulting service provider. We got some categories of reorts for our customer to subscribe. Every week we will generate a newsletter for the new reports of this week.
Customers can subscribe one or many categories of reports. And reoprts can be assigned to one or many categories. So I use master-detail tables to design the "customer-subscription" and "report-assign" relationship.
When we generate the weekly newsletter, we would like to figure out how many newsletters we have to build. For example, A company subscribes cate1, cate2. B company also subscribes cate1, cate2. They will receive the same weekly newsletters.
I think your solution can make this work. And I also want to know "can we do it only by simple SQL statements".
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply