locked
Merging Every 3 rows into a single row RRS feed

  • Question

  • I have 2 columns (ID, Msg_text) in a table where i need to combine every 3 rows into single row. What would be the best option i have? I know by using 'STUFF' and 'XML PATH' i can convert all the rows into a single row but here i'm looking for every 3 rows into a single row.



    • Edited by KK99 Tuesday, August 18, 2015 7:41 PM
    Tuesday, August 18, 2015 7:40 PM

Answers

  • create table test (id int, Msg_text varchar(100))
    insert into test values(1,'00000001 abc'),(2,'00000002 abc'),(3,'00000003 abc'),(4,'00000004 abc'),(5,'00000005 abc'),(6,'00000006 abc'),
    (7,'00000007 abc'),(8,'00000008 abc'),(9,'00000009 abc'),(10,'000000010 abc'),
    
    (11,'00000001 abc'),(12,'00000002 abc'),(13,'00000003 abc'),(14,'00000004 abc'),(15,'00000005 abc'),(16,'00000006 abc'),
    (17,'00000007 abc'),(18,'00000008 abc'),(19,'00000009 abc'),(20,'000000010 abc')
    
    
    ;with mycte as (Select id,Msg_text ,((Row_number() Over(Order by ID ) -1)  / 3 )  rn from test)
    
    
    SELECT t1.rn,
           Stuff(( SELECT ',' + Msg_text
               FROM mycte t2
              WHERE t2.rn = t1.rn  
              ORDER BY id
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
      FROM mycte t1
     GROUP BY t1.rn
    
    drop table test

    • Proposed as answer by Naomi N Tuesday, August 18, 2015 8:13 PM
    • Marked as answer by KK99 Tuesday, August 18, 2015 10:29 PM
    Tuesday, August 18, 2015 8:08 PM

All replies

  • create table test (id int, Msg_text varchar(100))
    insert into test values(1,'00000001 abc'),(2,'00000002 abc'),(3,'00000003 abc'),(4,'00000004 abc'),(5,'00000005 abc'),(6,'00000006 abc'),
    (7,'00000007 abc'),(8,'00000008 abc'),(9,'00000009 abc'),(10,'000000010 abc'),
    
    (11,'00000001 abc'),(12,'00000002 abc'),(13,'00000003 abc'),(14,'00000004 abc'),(15,'00000005 abc'),(16,'00000006 abc'),
    (17,'00000007 abc'),(18,'00000008 abc'),(19,'00000009 abc'),(20,'000000010 abc')
    
    
    ;with mycte as (Select id,Msg_text ,((Row_number() Over(Order by ID ) -1)  / 3 )  rn from test)
    
    
    SELECT t1.rn,
           Stuff(( SELECT ',' + Msg_text
               FROM mycte t2
              WHERE t2.rn = t1.rn  
              ORDER BY id
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
      FROM mycte t1
     GROUP BY t1.rn
    
    drop table test

    • Proposed as answer by Naomi N Tuesday, August 18, 2015 8:13 PM
    • Marked as answer by KK99 Tuesday, August 18, 2015 10:29 PM
    Tuesday, August 18, 2015 8:08 PM
  • Thank you for the solution, but it is taking time to get the result. All i have is 200000 records. Do you have any idea why it is taking too much time?



    • Edited by KK99 Tuesday, August 18, 2015 10:31 PM
    Tuesday, August 18, 2015 10:30 PM
  • Since it's joining CTE to itself it may not perform well. In this particular case the CLR approach may be better. Also, if you don't have any special chars in your Msg_Text you may drop this part and only use XML PATH('')
    ), TYPE).value('.', 'varchar(max)'),1,1,'')

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, August 18, 2015 10:45 PM