本文介绍了SQL Server 2008将行连接到列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用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将行连接到列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!