Check this:
sql - Concatenate many rows into a single text string? - Stack Overflow[
^]
sql - Multiple rows to one comma-separated value - Stack Overflow[
^]
[EDIT]
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
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