Sqlserver2005中group by中包含串连的ID---测试可用

How do I get:

id       Name       Value

1          A          4

1          B          8

2          C          9

to

id          Column

1          A:4, B:8

2          C:9

 

CREATE TABLE #YourTable ( [ID] INT, [Name] CHAR(1), [Value] INT )

 

INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'A', 4)

INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'B', 8)

INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (2, 'C', 9)

 

SELECT [ID],

       REPLACE(REPLACE(REPLACE(

                          (SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A

                           FROM   #YourTable

                           WHERE  ( ID = Results.ID )

                           FOR XML PATH (''))

                        , '</A><A>', ', ')

                ,'<A>','')

        ,'</A>','') AS NameValues

FROM   #YourTable Results

GROUP  BY ID

 

DROP TABLE #YourTable

 

 

http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server