Concatenate Column values from a table

Question

Please consider the following temp table:

DocID  Object  Field Value
--------------------------
 1      1       1        A
 2      1       1        B
 3      1       1        C
 4      1       2        A
 5      1       2        B
 6      2       1        F
 7      2       2        G
 8      2       2        H
 9      3       1        X
10      3       2        Y
11      3       3        Z
12      3       3        Z
13      3       3        Z

I would like to get the following result from a query:

Object  Field  Values
---------------------
1       1      A;B;C
1       2      A;B
2       1      F
2       2      G;H
3       1      X
3       2      Y
3       3      Z

In other words:

I would like to concatenate all values for each field for each object.

If for the same object for the same field, the same value exists (only the DocID is different), then the result should only show this repeating value once. Without this requirement, the result would look like:

Object  Field  Values
---------------------
1       1      A;B;C
1       2      A;B
2       1      F
2       2      G;H
3       1      X
3       2      Y
3       3      Z;Z;Z  <------- repeating values

In any scripting language, I would put the #temptable in an array and loop through it, but this time I have to accomplish this in SQL (Server 2008).

Is this even possible? Does SQL Server support looping through a #temptable or any other resultset and if so, could you give me pointers as how to solve this problem?

Any help is greatly appreciated.

Answer 1

You can use SQL Server's XML Extensions to do this:

SELECT  Object,
        Field,
        [Values] = STUFF((SELECT ';' + Value
                        FROM #TempTable AS T2
                        WHERE T2.Object = T.Object
                        AND T2.Field = T.Field
                        FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)'), 1, 1, '')

FROM    #temptable AS T
GROUP BY Object, Field;

For a more detailed explanation of how this method works see this answer

EDIT

If you only want unique values in the Values column, then you can just add DISTINCT to your subquery:

SELECT  Object,
        Field,
        [Values] = STUFF((SELECT DISTINCT ';' + Value
                        FROM #TempTable AS T2
                        WHERE T2.Object = T.Object
                        AND T2.Field = T.Field
                        FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)'), 1, 1, '')

FROM    #temptable AS T
GROUP BY Object, Field;