SQL coalesce problems

[ZPhantom]

Hi folks,
having some trouble, i have a database table that tooks like the following:

Assessment || Product Sold
1 || DRD
1 || CRD
2 || GRX
3 || TTR
3 || DRD
3 || GRX
etc...

I dont know how many entries each assessment will have and im aiming for a table like the following:

Assessment || Products
1 || DRD, CRD
2 || GRX
3 || TTR, DRD, GRX

etc...

the code im currently using is below: however its returning the hightest value from the assessment column, and all of the products in one string:

Many thanks

ZP

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]declare[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @Name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]max[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]declare[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @assId [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE]
[/COLOR][/SIZE]
 
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]with[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] test1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] A[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]AssessmentID [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [AassID] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] PT[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][Assessment] A[/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]LEFT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][AssessmentProductType] APT[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] A[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][AssessmentID] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] APT[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][AssessmentID][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]LEFT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][ProductType] PT[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] APT[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ProductTypeID] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] PT[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][ProductTypeID][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]@Name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]coalesce[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]coalesce[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@Name [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]', '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]cast[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]test1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @Name[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]@AssId [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] test1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]AassID[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Test1[/SIZE]
 
 
 
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]@assId [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [Assessment ID][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]@Name [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [Name] [/SIZE]