1

I have below table and string aggregation using XML:

            CREATE TABLE TestTable ([ID] INT, [Name] CHAR(1))


            INSERT INTO TestTable ([ID],[Name]) VALUES (1,'A')
            INSERT INTO TestTable ([ID],[Name]) VALUES (2,'B')
            INSERT INTO TestTable ([ID],[Name]) VALUES (3,'C')
            INSERT INTO TestTable ([ID],[Name]) VALUES (1,'D')
            INSERT INTO TestTable ([ID],[Name]) VALUES (1,'E')
            INSERT INTO TestTable ([ID],[Name]) VALUES (2,'F')
            INSERT INTO TestTable ([ID],[Name]) VALUES (3,'G')
            INSERT INTO TestTable ([ID],[Name]) VALUES (4,'H')

            SELECT 
              [ID],
              STUFF((
                SELECT '   ' + [Name] 
                FROM TestTable 
                WHERE (ID = Results.ID) 
                FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
              ,1,2,'') AS Names
            FROM TestTable Results
            GROUP BY ID

I get below result

        ID  Names
        1    A   D   E
        2    B   F
        3    C   G
        4    H

However i have requirement to limit number of Names to two, if its more than two, it should split to next row, Something like below. Here for ID=1, there were 3 Names, so 3rd name split into next row. How can i achieve this requirement

    ID  Names
    1    A   D
    1    E
    2    B   F
    3    C   G
    4    H

Thanks

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Bhim Rathor
  • 60
  • 5
  • `STUFF` does nothing but remove a trailinig separator. What you're doing is aggregating strings using XML. If you use SQL Server 2017+, use `STRING_AGG` – Panagiotis Kanavos Jan 25 '21 at 15:19

1 Answers1

2

It sounds like you want conditional aggregation, with up to two name columns per row. You can do this using row_number() and some arithmetic:

select id,
       max(case when seqnum % 2 = 1 then name end) as name1,
       max(case when seqnum % 2 = 0 then name end) as name2
from (select t.*,
             row_number() over (partition by id order by name) as seqnum
      from testtable t
     ) t
group by id, ceiling(seqnum / 2.0)
order by id, min(seqnum);

Here is a db<>fiddle.

I should note that you can concatenate these into a single column. I don't see a reason to do so, because you know the maximum number on each row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786