如何在SQL Server 2008中将分组中的列数据分组 [英] How to get column data separated by coma in grouping in SQL server 2008

问题描述

大家好，



i在sql中有一张桌子

Hi guys,

i have one table in sql

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

i希望在类型上做它的组，但是在变化中它应该显示与其类型相关的所有数据。



例如：for apple ，它必须显示。 gala，fuji，limbertwing



我尝试过：



我想要显示这个结果。

i want to do its group on type, but in variety it should show all data related to its type.

for eg: for apple, it must show. gala, fuji, limbertwing

What I have tried:

I want to display this as a result.

+--------+----------------------------+-------+
| type   | variety                    | price |
+--------+----------------------------+-------+
| apple  | gala, fuji, limbertwig     |  5.9  | 
| orange | valencia,navel             | 12.95 |  
| pear   | bradford,bartlett          |  8.19 | 
| cherry | bing, chelan               |  8.88 | 
+--------+----------------------------+-------+

谢谢

Thanks

推荐答案

尝试：

SELECT Type, Variety =
   STUFF((SELECT ',' + Variety
      FROM myTable b
      WHERE b.Type = a.Type
      FOR XML PATH('')), 1, 1, ''),
   SUM(Price) AS Price
FROM myTable a
GROUP BY Type

您好您可以使用此查询：



选择YT.type，

（

STUFF（（SELECT'，'+ EQ.variety FROM YourTable EQ WITH（NOLOCK）WHERE EQ.type = YT.type FOR XML PATH（''）），1,1，''）

）品种，



总和（YT.price）价格



来自yourtable YT

按类型分组





如果您遇到任何问题，请与我们联系。



Ashish Nigam

Hi you can use this query:

select YT.type,
(
STUFF((SELECT ',' + EQ.variety FROM YourTable EQ WITH (NOLOCK) WHERE EQ.type=YT.type FOR XML PATH('')), 1, 1, '')
) variety,

sum(YT.price) Price

from yourtable YT
group by type


Please let me know if you are getting any problem.

Ashish Nigam

这篇关于如何在SQL Server 2008中将分组中的列数据分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！