SQL Server 2008将行连接到列 [英] SQL Server 2008 concatenate rows to column

问题描述

我正在使用SQL Server 2008，我有一个数据集，如下所示：

I am using SQL Server 2008, I have a dataset that look like:

FormKey        Value      Category   
-------        -----      ------
123456         Gloves     PPE
123456         Hat        PPE
123456         Scalf      PPE
123456         Boots      PPE
987654         Glasses    PPE
987654         Harness    PPE
987654         Overalls   PPE

我要连接值和按FormKey分组，以便最终得到：

I am trying to concatenate the Values and group by FormKey, so that I would end up with:

Formkey       Value                        Category
-------       -----                        -------
123456        Gloves, Hat, Scalf, Boots     PPE
987654        Glasses, Harness, Overalls    PPE

但是，我得到了每个Formkey的所有值的组合。

However, I am getting a concat of ALL of the Values for each of the Formkeys.

我一直在使用的代码是：

The code I have been using is:

SELECT frd.formresultkey AS frk
    ,STUFF((
            SELECT ', ' + fra.value
            FROM [FormResultAnswers] FRA
            INNER JOIN [FormResultDetails] FRD ON FRA.[DetailKey] = FRD.[DetailKey]
            INNER JOIN [FormResults] FR ON FRD.[FormResultKey] = FR.[FormResultKey]
            WHERE FR.FormReference = 'PPE'
                AND frd.FormElementReference = 'PPE_List'
            FOR XML path('')
            ), 1, 1, '') AS Concatted
FROM [FormResultAnswers] FRA
INNER JOIN [FormResultDetails] FRD ON FRA.[DetailKey] = FRD.[DetailKey]
INNER JOIN [FormResults] FR ON FRD.[FormResultKey] = FR.[FormResultKey]

在此之后，我需要使用Formkey匹配的连接值更新表。有人可以帮忙吗？

After this I need to update a table with the concatenated value where the Formkeys match. Can anyone help please?

推荐答案

对查询的某些修改将获取结果。试试这个。

Some modification to your query will fetch the result. Try this.

SELECT FormKey,
       Stuff((SELECT ',' + Value
              FROM   Result b
              WHERE  a.FormKey = b.FormKey
                     AND a.Category = b.Category
              FOR xml path('')), 1, 1, '') value,
       Category
FROM   Result a
GROUP  BY FormKey,
          Category 

这篇关于SQL Server 2008将行连接到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！