Concatanating Pairs of Rows SQL Server

Question

I have the following table, sorted.

ID    Value     Amount
1       A        10.00
2       B         4.25 
3       C         2.01
4       D         5.00

How can I concatenate only consecutive pairs of rows and turn it to this:

ID    Col1      Col2 
1,2    A,B     10.00,4.25 
3,4    C,D     2.01,5.00

And I don't want to use user-defined tables or temp tables. I am open to using the window functions provided in SQL Server 2012 and 2014 though.

voting for reopening. Due to the nature of this question with only 2 rows being combined, it can be solved with a very different syntax. — t-clausen.dk, Mar 02 '15 at 10:00

t-clausen.dk · Accepted Answer · 2015-03-02T09:21:30.563

I looked at the other solutions and thought it was overkill, so I reused some and excluded or rewrote the unnecessary parts. This should result in better performance.

;WITH cte (rn, id, Value, Amount)
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY id), id, Value, Amount
    FROM yourtable
)
SELECT 
    ( SELECT CAST(T.id AS VARCHAR(10)) + ','+ CAST(T1.id AS VARCHAR(10))
      FROM cte AS T1
      WHERE T1.rn = T.rn + 1) ID,
    ( SELECT CAST(T.value AS VARCHAR(10)) + ','+ CAST(T1.value AS VARCHAR(10))
      FROM cte AS T1
      WHERE T1.rn = T.rn + 1) COL1,
    ( SELECT CAST(T.Amount AS VARCHAR(10)) + ','+ CAST(T1.Amount AS VARCHAR(10))
      FROM cte AS T1
      WHERE T1.rn = T.rn + 1) COL2
FROM cte AS T
WHERE rn % 2 = 1

score 0 · Answer 2 · answered Mar 02 '15 at 06:16

Try this,if it do not work with other sample data then let me know,

Declare @t table(ID varchar(50), Value varchar(50), Amount float)
insert into @t values(1,'A',10.00),(2,'B', 4.25),(3,'C',2.01)
,(4,'D',5.00 )
--Get the maxid
declare @MaxID int=(Select max(id) from @t)

;WITh CTE AS
(
 SELECT ID,
STUFF((select ','+ id from @t where id IN(1,2) for xml path('')),1,1,'') [id1]
,STUFF((select ','+ Value from @t where id IN(1,2) for xml path('')),1,1,'') [Value]
,STUFF((select ','+ cast(Amount as varchar) from @t where id IN(1,2) for xml path('')),1,1,'') [Amount]
,1 RN
FROM @T a WHERE ID=1
UNION ALL
SELECT B.ID,
STUFF((select ','+ id from @t where id IN(RN+2,RN+3) for xml path('')),1,1,'') 
,STUFF((select ','+ Value from @t where id IN(RN+2,RN+3) for xml path('')),1,1,'')
,STUFF((select ','+ cast(Amount as varchar) from @t where id IN(RN+2,RN+3) for xml path('')),1,1,'') 
, RN+2
FROM cte a 
CROSS APPLY(SELECT * FROM @T WHERE ID=RN+2 AND ID<=@MaxID) B 


)

SELECT ID1,Value,Amount FROM CTE

score 0 · Answer 3 · answered Mar 02 '15 at 06:59

This works just fine.. We just generate unique IDs for each pair using row_number and concatenate using FOR XML PATH on these IDs:

DECLARE @Test TABLE
(
     ID VARCHAR(50)
    , Value VARCHAR(50)
    , Amount FLOAT
);

INSERT INTO @Test
    (ID, Value, Amount)
VALUES 
      (1, 'A', 10.00)
      , (2, 'B', 4.25)
      , (3, 'C', 2.01)
      , (4, 'D', 5.00);

;WITH cte (rn, id, Value, Amount)
AS
(
    SELECT (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2, id, Value, Amount
    FROM @Test
)
SELECT DISTINCT
    STUFF((SELECT ',' + CAST(T1.id AS VARCHAR(10))
            FROM cte AS T1
            WHERE T1.rn = T.rn
            ORDER BY T1.id
            FOR XML PATH('')), 1, 1, '') AS ID
    , STUFF((SELECT ',' + CAST(T2.Value AS VARCHAR(10))
            FROM cte AS T2
            WHERE T2.rn = T.rn
            ORDER BY T2.id
            FOR XML PATH('')), 1, 1, '') AS Col1
    , STUFF((SELECT ',' + CAST(T3.Amount AS VARCHAR(10))
            FROM cte AS T3
            WHERE T3.rn = T.rn
            ORDER BY T3.id
            FOR XML PATH('')), 1, 1, '') AS Col2
FROM cte AS T

Output:

ID  Col1    Col2
-------------------
1,2 A,B     10,4.25
3,4 C,D     2.01,5

Concatanating Pairs of Rows SQL Server

3 Answers3