SQL Server 2005 query columns to Rows with a twist

Question

Here is my table:

 CODE     __NAME
 1000     ___AB
 1001     ___CA
 1001     ___DR
 1001     ___LVN
 1100     ___ER
 1110     ___WL

What I want to get to is:

 CODE___NAME
 1000  ___AB
 1001  ___CA,DR,LVN
 1100  ___ER
 1110  ___WL

I know I can use looping to accomplish this but I was wondering if there might be a better way. Thanks

Answer 1

Try something like this, this will get you what you want.

   Select 
        Main.CODE,
        Left(Main.Names,Len(Main.Names)-1) As Names
    From
        (
        Select distinct T2.CODE, 
        (
         Select 
            RTRIM(T1.NAME) + ', ' AS [text()]
         From   
            Test T1
         Where
            T1.CODE = T2.CODE
         ORDER BY 
            T1.CODE
         For XML PATH ('')
        ) [Names]
        From Test T2
        ) [Main]