Agfdhyk

Consider two tables, one containing the details of work (cases) to be carried out, and one describing what work has been performed on each cases (activities).

The cases table is roughly 20million rows.

CREATE TABLE #cases

    (CASEID int, DETAILS varchar(1))



INSERT INTO #cases

    (CASEID, DETAILS)

VALUES

    (1, 'A'),

    (2, 'B'),

    (3, 'C')

;

The activities table is roughly 180million rows.

CREATE TABLE #activities

    (ACTIVITYID int, CASEID int, CODE varchar(3), STARTDATE date)



INSERT INTO #activities

    (ACTIVITYID, CASEID, CODE, STARTDATE)

VALUES

    (1, 1, '00', '2018-01-01'),

    (2, 1, '110', '2018-02-01'),

    (3, 1, '900', '2018-03-01'),

    (4, 1, '910', '2018-05-01'),

    (5, 1, '920', '2018-04-01'),

    (6, 2, '900', '2018-01-01'),

    (7, 2, '110', '2018-02-01'),

    (8, 2, '900', '2018-03-01'),

    (9, 3, '00', '2018-01-01'),

    (10, 3, '123', '2018-02-01')

;

It is not ideal - but I need to find a way to create a wide table containing case details, and then details of the first 10 activities with a code in the range 900-999.

Some cases will have more than 10 activities in that range - some cases will have none.

The output I am looking for is something along the lines of:

CASEID  DETAILS CODE1st900  STARTDATE1st900 CODE2nd900  STARTDATE2nd900 CODE3rd900  STARTDATE3rd900

1   A   900 01/01/2018 00:00:00 920 01/04/2018 00:00:00 910 01/05/2018 00:00:00

2   B   900 01/01/2018 00:00:00 900 01/03/2018 00:00:00     

3   C       

Ultimately I am not sure whether some kind of clever pivot is the best approach here, joining each set of values with a subquery, or a cursor which is typically how my organisation has created this sort of data previously.

DBFiddle to play with here:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5eef2de402726218a8472880ef0bab85

Given the volume of data, I would do this using apply:

select c.*, a.*

from cases c outer apply

     (select max(case when seqnum = 1 then code end) as code_1,

             max(case when seqnum = 1 then startdate end) as startdate_1,

             max(case when seqnum = 2 then code end) as code_2,

             max(case when seqnum = 2 then startdate end) as startdate_2,

             . . .

      from (select top (10) a.*,

                   row_number() over (partition by a.caseid order by a.startdate) as seqnum

            from activities a

            where a.caseid = c.caseid and

                  a.code between 900 and 999

           ) a

      ) a;

This should have much better performance than solutions using pivot or group by, because the data from cases does not need to be aggregated. The aggregations are taking place ten rows at a time as needed.

Usually we'd prefer PIVOT but there's no syntax currently for pivoting multiple columns simultaneously. So we'll use conditional aggregation instead:

declare @cases table (CASEID int, DETAILS varchar(1))

INSERT INTO @cases (CASEID, DETAILS) VALUES

(1, 'A'),

(2, 'B'),

(3, 'C');



declare @activities table (ACTIVITYID int, CASEID int, CODE varchar(3), STARTDATE date)

INSERT INTO @activities (ACTIVITYID, CASEID, CODE, STARTDATE) VALUES

(1, 1, '00', '2018-01-01'),

(2, 1, '110', '2018-02-01'),

(3, 1, '900', '2018-03-01'),

(4, 1, '910', '2018-05-01'),

(5, 1, '920', '2018-04-01'),

(6, 2, '900', '2018-01-01'),

(7, 2, '110', '2018-02-01'),

(8, 2, '900', '2018-03-01'),

(9, 3, '00', '2018-01-01'),

(10, 3, '123', '2018-02-01');



select

    c.CASEID,

    c.DETAILS,

    MAX(CASE WHEN rn=1 THEN CODE END) as Code1st,

    MAX(CASE WHEN rn=1 THEN STARTDATE END) as Start1st,

    MAX(CASE WHEN rn=2 THEN CODE END) as Code2nd,

    MAX(CASE WHEN rn=2 THEN STARTDATE END) as Start2nd

from

    @cases c

        left join

    (select *,ROW_NUMBER() OVER (PARTITION BY CASEID ORDER BY STARTDATE) rn

     from @activities

     where CODE BETWEEN 900 and 999) a

        on

            c.CASEID = a.CASEID and

            a.rn <= 10

group by c.CASEID,c.DETAILS

I've shown pivoting the first pair of pairs above. Hopefully you can see how it extends for the remaining 8.