This solution utilizes features introduced in SQL 2005. It starts off with a CTE (common table expression) of all of the distinct AccountNumbers in the table. For each AccountNumber, we get a comma separated list of the Value field, sorted by the Value field.
WITH CTE AS
(
SELECT DISTINCT
AccountNumber
FROM #TestData
)
SELECT AccountNumber,
CommaList = STUFF((
SELECT ',' + Value
FROM #TestData
WHERE AccountNumber = CTE.AccountNumber
ORDER BY Value
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY AccountNumber;
The key to creating the comma separated list is the correlated subquery. Working from the inside out, we get each value prefixed with a comma, order by the Value. The FOR XML PATH('') generates an XML structure, with an empty string as the root node. Since the field is ',' + Value (an unnamed expression), there is no name for the individual elements. What is left is a list of values, with each value prefixed with a comma. The TYPE clause specifies to return the data as an XML type. The .value('.','varchar(max)') takes each value, and converts it into a varchar(max) data type. The combination of the TYPE and .value means that values are created at XML tags (such as the ampersand (&), and the greater than (>) and less than (<) signs), will not be tokenized into their XML representations and will remain as is.
At this point, you will have a comma separated list of all values starting with a comma for each value. All that remains is to remove the very first leading comma from the entire string. To do this, we utilize the STUFF function. Using the string created by the FOR XML PATH(''), TYPE, and starting with the first character, we replace one character (the leading comma) with an empty string. (Note that if you wanted the string to be separated with a comma and a space, you would specify ', ', and replace 2 characters in the STUFF function with an empty string.
The subquery is correlated, meaning that it references a value outside of itself to control what it is doing. In this case, it is referencing the current AccountNumber from the CTE.
The results will look like this (abbreviated due to length)
AccountNumber CommaList ------------- -----------------------------------------------------------------------------
1 @@H,@BE,@CE,@DA,@FA,@FH,@GB,@GD,@HC,@HG,A@E,A@G,AEC,AH@,AHB,AHI,AIG,B@@,B@A,B@B,BBG,B, ...
2 @CB,@CE,@CG,@DB,@EE,@GG,@GG,@HC,@IF,A@E,AAF,AAI,ACG,AEA,AFA,AFB,AFC,AFC,AFI,AGF,AIE,AIH,B ...
3 @@E,@@H,@BE,@CD,@DC,@DI,@EF,@EI,@FB,@GE,A@@,AAE,ACE,AEF,AFA,AGC,AH@,AIH,B@C,BAI,BC@,BDF, ...
