我想编写SQL查询来获取结果组。 [英] I want to write SQL query to get group of result.

问题描述

Table
Column1	Column2
a	      1
a	      2
a	      3
a	      4
a	      5
a	      6
a	      7
a	      8
a	      9
a	      10

 Required OUT PUT: 
Column1	Column2
a	       1,2,3,4,5
a	        6,7,8,9,10

我尝试了什么：



我试图使用sql server获得低于结果但是没有得到例外结果。

请帮忙。

What I have tried:

I have tried to get below result using sql server but don't get excepted result.
Please help.

推荐答案

检查一下：

sql - 将多行连接成一个文本字符串？ - Stack Overflow [ ^ ]

sql - 多行到一个以逗号分隔的值 - Stack Overflow [ ^ ]







我忘了提到你需要使用 NTILE（2） [ ^ ]函数能够在开始连接 Column2 之前将 Column1 拆分为2组：

Check this:
sql - Concatenate many rows into a single text string? - Stack Overflow[^]
sql - Multiple rows to one comma-separated value - Stack Overflow[^]



I forgot to mentiond that you need to use NTILE(2)[^] function to be able to split Column1 into 2 groups, before you start concatenating Column2:

SELECT *, NTILE(2) OVER( ORDER BY Column1) AS GrpNo
FROM YourTableName

< br $> b $ b

完整示例：

Complete example:

DECLARE @tmp TABLE (Column1 VARCHAR(5),	Column2 INT)

INSERT INTO @tmp (Column1, Column2)
VALUES('a', 1),
('a', 2),
('a', 3),
('a', 4),
('a', 5),
('a', 6),
('a', 7),
('a', 8),
('a', 9),
('a', 10)

SELECT DISTINCT t2.Column1, 
    (
        SELECT CONVERT(VARCHAR(100), t1.Column2) + ',' AS [text()]
        From (
			SELECT *, NTILE(2) OVER( ORDER BY Column1) AS GrpNo
			FROM @tmp
		) t1
        WHERE t1.Column1  = t2.Column1 AND t1.GrpNo = t2.GrpNo
        FOR XML PATH ('')
    ) AS Col2
FROM (
	SELECT *, NTILE(2) OVER( ORDER BY Column1) AS GrpNo
	FROM @tmp
	) AS t2

这篇关于我想编写SQL查询来获取结果组。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！