for xml path concatenation ordered by seqno, Sql Server

for xml path concatenation ordered by seqno

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE Table1
    ([Cus] int, [_dupe_num] int, [ActivityDate] datetime, [ActivityCode] varchar(4), [Collector] varchar(3), [ActivityTime] int, [ActivityNotes] varchar(50), [SeqNo] varchar(11))
;
    
INSERT INTO Table1
    ([Cus], [_dupe_num], [ActivityDate], [ActivityCode], [Collector], [ActivityTime], [ActivityNotes], [SeqNo])
VALUES
    (4293312, 1, '2016-11-17 00:00:00', 'COMM', 'TW1', 16261, 'TCPA GIVEN ON 11/04/16', '0000079663D'),
    (4293312, 1, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'APP - QA-VI/ALL MM ADVSD 27 DPD $312.20 TAD APP SD', '0000079663F'),
    (4293312, 2, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'CO/APP WLL BE MKNG PYMNT ON PAY DAY AND THINKS HE', '0000079663G'),
    (4293312, 3, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'GETS PAID TMRRW, I ADVSD TO CLL CO/APP WHN HE TKES', '0000079663H'),
    (4293312, 4, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'A BREAK AND HVE HIM CLL BEFORE TMRRW AND HE SD CO/', '0000079663I'),
    (4293312, 5, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'APP WLL CLL WHN HE GETS HIS CHECK AND CASHES IT, I', '0000079664{'),
    (4293312, 6, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'ADVSD ANTHR DUE DATE AND CAN NOT CNTNUE TO CARRY', '0000079664A'),
    (4293312, 7, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'LOAN THIS WAY, HE STARTED USING FOUL LANGUAGE AND', '0000079664B'),
    (4293312, 8, '2016-11-17 00:00:00', 'PBCC', 'TW1', 16252, 'SD WE''LL GET OUR MONEY AND HUNG UP', '0000079664C'),
    (4293312, 1, '2016-11-17 00:00:00', 'PCNA', 'TW1', 16091, 'APP - NO ANSWER', '0000079664I'),
    (4293312, 1, '2016-11-17 00:00:00', 'PCNA', 'TW1', 16120, 'CO/APP - NO ANSWER', '0000079664G'),
    (4293312, 1, '2016-11-17 00:00:00', 'PHLM', 'TW1', 16132, 'CO/APP - FEMALE SD APP WNT TO WRK WLL GVE MSG', '0000079664E')
;

SELECT distinct
      cus
    , ActivityDate
    , ActivityCode
    , Collector
    , ActivityTime
    , STUFF
      ((
            SELECT
                  ', ' + a.ActivityNotes
            FROM table1 a
            WHERE (a.cus = b.cus
                  AND a.ActivityDate = b.ActivityDate
                  AND a.ActivityCode = b.ActivityCode
                  AND a.ActivityTime = b.ActivityTime
                  )
            order by SeqNo
            FOR xml PATH ('')
      )
      , 1, 2, '')
      AS cusr
FROM table1 b


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development