question

Ian Roke avatar image
Ian Roke asked

Crosstab but with a twist!

I have the following example table structure:

PolicyNumber              CaptureDate             DemandText                                                                                           SubDemandText
------------------------- ----------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
TK91019744                2009-08-13 14:10:00     I want to change my policy details                                                                   Defering retirement
1467338UG                 2010-06-07 15:35:00     I want a value                                                                                       CFV/NPR/PR/No of Units Held/Bid Price
1467338UG                 2010-06-07 15:35:00     I want With Profit/MVR information                                                                   What is it?/Does it apply?/How much?
1467338UG                 2010-06-07 15:35:00     I want With Profit/MVR information                                                                   Bonus Rates/Amounts (Regular/Final/Special)
1516678UX                 2010-06-08 15:23:00     Chase - Payments/Money Orientated                                                                    Death Claim
1563576UX                 2010-06-10 10:03:00     I want policy / contract information                                                                 What is the policy no
1563576UX                 2010-06-10 10:03:00     I want policy / contract information                                                                 Policy Type
1563576UX                 2010-06-10 10:03:00     I want a value                                                                                       FV, SV, MVR, Units, Bid Price, Final Bonus
1563576UX                 2010-06-10 10:03:00     I want policy / contract information                                                                 Initial Investment/Policy Start Date
1563576UX                 2010-06-10 10:04:00     I want fund information                                                                              MVR guarantee
1601369UJ                 2010-04-23 10:34:00     I want a value                                                                                       FV, SV, MVR, Units, Bid Price, Final Bonus
1601369UJ                 2010-04-23 10:35:00     I want policy / contract information                                                                 Withdrawals / History
1630285UJ                 2010-04-12 15:54:00     I want a value                                                                                       FV, SV, MVR, Units, Bid Price, Final Bonus
1630285UJ                 2010-04-12 15:55:00     I want policy / contract information                                                                 Withdrawals / History

Now for some reason a customer wants the data in the following format for a report:

PolicyNumber  CaptureDate  DemandText  SubDemandText  CaptureDate  DemandText  SubDemandText  CaptureDate  DemandText  SubDemandText
------------  -----------  ----------  -------------  -----------  ----------  -------------  -----------  ----------  -------------

Pivotted data but they want the CaptureDate, DemandText and SubDemandText repeated for each line across in columns so the first demand is first then the next and so on. I hope I have explained this well enough. If I haven't give me a shout.

sql-server-2005t-sql
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I see you've shown the output running to 3 sets of CaptureDate / Demand Text / SubDemandText, and yet some of your data has 5 rows. Do you want more columns returning, or a second row of data?
0 Likes 0 ·
I require it to go as far as there are rows for each Policy Number. For example the maximum I've seen so far is 15 for one policy.
0 Likes 0 ·
@Ian - I added a dynamic SQL solution to my answer, which handles the columns as needed. I tested the solution with the close clone of your data, and it looks like it works properly.
0 Likes 0 ·
Oleg avatar image
Oleg answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered

Ian, most reporting tools will create multicolumn, repeating columns this way. For example, SSRS calls this a "Newsletter-Style Report."

If you are forced to do this with pure SQL, then I would create a temp table, duplicate the columns (PolicyNumber1, ... Policyumber2) and then fill them in manually. But there may be a better way...

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks however as stated in the tags I am having to use T-SQL.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.