Convert rows to columns dynamically after a join

Question

I have the following tables:

declare @Risks table(id int identity, name varchar(20))
declare @Mitigations table(id int identity, riskId int, mitigation_desc varchar(20), record_date datetime)

insert into @Risks(name)
select 'Risk 1' union all
select 'Risk 2' union all
select 'Risk 3'

insert into @Mitigations(riskId, mitigation_desc, record_date)
select 1, 'Mitigation 1', '2016-01-01' union all
select 1, 'Mitigation 2', '2016-01-01' union all
select 2, 'Mitigation 3', '2016-01-01' 

SELECT  *
FROM    @Risks a
LEFT OUTER JOIN  @Mitigations b ON a.id = b.riskId

Each risk can have more than 1 mitigation, there is no limit, what I would like to do is to be able to display all of the mitigations of each risk horizontally like this:

ID  Name   mitigation_desc      record_date   mitigation_desc  record_date 
--- -----  ------------------  -------------  ---------------- -------------
1   Risk 1  Mitigation 1        2016-01-01    Mitigation 2       2016-01-01
2   Risk 2  Mitigation 3        2016-01-01    NULL               NULL
3   Risk 3  NULL                NULL          NULL               NULL

I have tried a few scenarios using pivots and joins but none of them is flexible enough as a risk can have number of mitigation. Is there any other way of achieving this?

To make things easier, I could restrict the number of mitigations displayed, for instance always display 3 sets of mitigations per risk.

Thanks

Where are you displaying this data? This seems like much more of a presentation issue than a data extract issue. — iamdave, Nov 15 '16 at 13:12
The short answer is `No`. SQL requires you to know how many columns *(and their names, etc)* are being returned *in advance*. Your two options are to write code that writes SQL *(query you table to determine how many columns you need, then generate the SQL you need to cope with that situation)*. Or *(this is the better option, as it fits the normalise RDBMS patterns)* simply return the table without pivotting, and then pivot it in your application / reporting layer. *(A general rule of thumb is that Presentation issues should not be solved in your data layer.)* — MatBailie, Nov 15 '16 at 13:13
Dynamic SQL & a Pivot Statement. This type of question has been asked many times on SO. Here's a good example: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query; but I agree with @iamdave formatting of data is a presentation issue. — xQbert, Nov 15 '16 at 13:14
ok, thanks, what if I have limited the number of mitigations returned to say only 2. I have updated the question — 03Usr, Nov 15 '16 at 13:14
Limiting the number of mitigations will simplify the problem. But does it make the output pointless (only you can answer this)? Is your report misleading if it omits mitigations 3 through *n*? — David Rushton, Nov 15 '16 at 13:26

score 1 · Accepted Answer · answered Nov 15 '16 at 13:27

If you only want to return a maximum of two mitigations, then I'd use the following...

WITH
   sorted_risks AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY riskID
                               ORDER BY record_date,
                                        mitigation_desc)  AS ordinal,
        *
    FROM
        @risks
)
SELECT
    riskID,
    MAX(CASE WHEN ordinal = 1 THEN mitigation_desc END)   AS mitigation_desc_1,
    MAX(CASE WHEN ordinal = 1 THEN record_date     END)   AS record_date_1,
    MAX(CASE WHEN ordinal = 2 THEN mitigation_desc END)   AS mitigation_desc_2,
    MAX(CASE WHEN ordinal = 2 THEN record_date     END)   AS record_date_2
FROM
    sorted_risks
WHERE
    ordinal <= 2
GROUP BY
    riskID

If you want the newest two (rather than oldest two) records, change the ORDER BY record_date, to be ORDER BY record_date DESC,.

Even so, I would still suggest that SQL is the wrong place to do this.

Your comments suggest that you're only doing this for Presentation reasons, in an MVC application. In which case, you should be doing this in the MVC application's presentation layer.

Convert rows to columns dynamically after a join

1 Answers1