SQL Server 2005查询具有扭曲的行的列

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

我知道我可以使用循环来完成这个,但我想知道是否有更好的方法。谢谢

1 个解决方案

#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]