move multiple codes in same row-field using a unique account no. 1

[ciarra41]

I have some duplicate account numbers but the codes are not. I’m trying to move each codes for the same account number into one field separated by commas

00629144 HAMR1
00629144 HUME2

Move the codes in the same row separated using a comma for the unique account number
00629797 HAMR, HUM

This is how I would do this in Access but I can’t figure it out on SQL Server,
SELECT QryAreasTS.ResourceID,
Concatenate("SELECT CharacteristicValue FROM QryZoneOnly WHERE ResourceID=""" & [ResourceID] & """")) AS Zones
FROM QryAreasTS
ORDER BY IIf([CharacteristicValue]=Null,"Manager",Concatenate("SELECT CharacteristicValue FROM QryZoneOnly WHERE ResourceID=""" & [ResourceID] & """"));

 

[markros]

SQL Server 2005 solution:

;with cte as (select Account from Accounts group by Account)

select cte.Account, stuff(F.Codes,1,2,'')
from cte CROSS APPLY 
(select ', ' + Code from Accounts where Accounts.Account = cte.Account) F

Explanations of this technique please see in

MSDN thread about concatenating rows

Making a list and checking it twice

Concatenating Rows - Part 1

Concatenating Rows - Part 2



PluralSight Learning Library