Thursday, September 3, 2015

Sql Server like STRAGG functionality

Making a tiny modification to this answer, you have STRAGG functionality in SqlServer:

CREATE TABLE yourtable
([FieldA] int, [FieldB] varchar(1))
;

INSERT INTO yourtable
([FieldA], [FieldB])
VALUES
(1, 'A'),
(1, 'B'),
(2, 'A')
;

select distinct t1.FieldA,
  STUFF((SELECT distinct ',' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') data
from yourtable t1;

As usual, however, this ended up making me raise another question, still waiting for an answer :-)

1 comment: