question

Here is the solution which exploits the cheat lookup (figure out which policy has the most records and go from there). Without the cheat, you can easily translate the statement below to the SQL generating the SQL and then execute it. Here is the script based on the maximum number of records = 5 per policy:

with records
(
    TheRank, RowNumber, PolicyNumber, 
    CaptureDate, DemandText, SubDemandText
) as
(
    select 
        dense_rank() over (order by PolicyNumber) TheRank,
        row_number() over (partition by PolicyNumber 
            order by PolicyNumber) RowNumber,
        PolicyNumber, CaptureDate, DemandText, SubDemandText
        from dbo.demand
)
select
    TheRank, max(PolicyNumber) PolicyNumber,
    max(case when RowNumber = 1 then CaptureDate 
        else null end) CaptureDate,
    max(case when RowNumber = 1 then DemandText 
        else '' end) DemandText,
    max(case when RowNumber = 1 then SubDemandText 
        else '' end) SubDemandText,
    max(case when RowNumber = 2 then CaptureDate 
        else null end) CaptureDate,
    max(case when RowNumber = 2 then DemandText 
        else '' end) DemandText,
    max(case when RowNumber = 2 then SubDemandText 
        else '' end) SubDemandText,
    max(case when RowNumber = 3 then CaptureDate 
        else null end) CaptureDate,
    max(case when RowNumber = 3 then DemandText 
        else '' end) DemandText,
    max(case when RowNumber = 3 then SubDemandText 
        else '' end) SubDemandText,
    max(case when RowNumber = 4 then CaptureDate 
        else null end) CaptureDate,
    max(case when RowNumber = 4 then DemandText 
        else '' end) DemandText,
    max(case when RowNumber = 4 then SubDemandText 
        else '' end) SubDemandText,
    max(case when RowNumber = 5 then CaptureDate 
        else null end) CaptureDate,
    max(case when RowNumber = 5 then DemandText 
        else '' end) DemandText,
    max(case when RowNumber = 5 then SubDemandText 
        else '' end) SubDemandText
    from records
    group by TheRank;

The downside of the solution above is clear as the columns are hardcoded. Instead, it can be better and easier to simply figure the desired columns on as needed basis, generate dynamic statement and execute it. Here is the dynamic SQL solution, which will work for any reasonable number of columns. Of course, if there is a policy with hundreds of entries then the number of columns will become ridiculously big rendering report very unfriendly. However, if the maximum number of entries per policy is reasonable then the solution below will work properly:

-- dynamic SQL solution to automatically create as
-- many column headers as needed for this report
declare @max_entries int;
declare @columns varchar(max);
declare @sql varchar(max);

-- find the policy with biggest number of entries
select 
    @max_entries = MaxEntries
    from 
    (
        select top 1 count(1) MaxEntries
            from dbo.demand
            group by PolicyNumber
            order by 1 desc
    ) t;

-- using quirky padding, populate @columns variable
select 
    @columns = isnull(@columns + ', ' + char(10), '') +
    'max(case when RowNumber = ' + Seed + ' then CaptureDate ' +
    'else null end) CaptureDate, ' + char(10) +
    'max(case when RowNumber = ' + Seed + ' then DemandText ' +
    'else '''' end) DemandText, ' + char(10) +
    'max(case when RowNumber = ' + Seed + ' then SubDemandText ' +
    'else '''' end) SubDemandText'
    from 
    (
        select top(@max_entries)
        cast(row_number() over (order by PolicyNumber) 
            as varchar(10)) Seed 
        from dbo.demand
    ) t;

-- add head and tail to complete the statement
set @sql = '
with records
(
    TheRank, RowNumber, PolicyNumber, 
    CaptureDate, DemandText, SubDemandText
) as
(
    select 
        dense_rank() over (order by PolicyNumber) TheRank,
        row_number() over (partition by PolicyNumber 
            order by PolicyNumber) RowNumber,
        PolicyNumber, CaptureDate, DemandText, SubDemandText
        from dbo.demand
)
select
    TheRank, max(PolicyNumber) PolicyNumber, 
' + @columns + ' 
    from records
    group by TheRank;';

-- execute the statement to get desired results
exec (@sql);
go

Please change the table name accordingly.

Hope this helps,

Oleg