SQL PIVOT - Multiple Dynamic Columns

Question

Problem

Need to submit a file to a provider that wishes to use columns for the data if there are multiples.

Our SQL DB logs 'claims' as rows like the examples below I have created:

PolRef@     Number  Type                Date                        Value
ABHX06HQ01  1       Escape Of Water     2009-05-01 00:00:00.000     840
ABHX06HQ01  2       Escape Of Water     2009-05-06 00:00:00.000     400
ABHX06HQ01  3       Escape Of Water     2010-02-01 00:00:00.000     304
ABHX06HQ01  4       Storm Damage        2010-02-11 00:00:00.000     59
ABHX06HQ01  5       Accidental Damage   2011-10-14 00:00:00.000     497
ABHX06HQ01  6       Falling Trees       2011-09-29 00:00:00.000     1172

The file I need to submit requires each of those claim rows to be columns so essentially the columns in SQL would go:

ClaimNumber1,   ClaimType1,         ClaimDate1,                 ClaimValue1,    ClaimNumber2,   ClaimType2,         ClaimDate2,                 ClaimValue2,    ClaimNumber3,   ClaimType3,         ClaimDate3,                 ClaimValue3
1               Escape Of Water     2009-05-01 00:00:00.000     840             2               Escape Of Water     2009-05-06 00:00:00.000     400             3               Escape Of Water     2010-02-01 00:00:00.000     304

This can quite possibly be a maximum of 10 although dynamic columns would be nice. I have a feeling this would be a PIVOT, although I have no idea where to even begin.

I started by doing a UNION for each WHERE Number = X but it seems extremely archaic although would eventually get me my result.

David Rushton · Accepted Answer · 2016-12-13 13:35:32Z

1

Although you can use the PIVOT operator I prefer conditional aggregation.

This technique uses one or more case expressions to split your rows into columns. The group by clause, combined with an aggregate function, then reduces the final row count.

Here is an example that uses the first three rows from your table.

-- Rows to columns
SELECT
    PolRef,
    CASE WHEN Number = 1 THEN [TYPE_ID] ELSE '' END AS Type_1,
    CASE WHEN Number = 2 THEN [TYPE_ID] ELSE '' END AS Type_2,
    CASE WHEN Number = 3 THEN [TYPE_ID] ELSE '' END AS Type_3
FROM
    YourTable
GROUP BY
    PolRef
;

Returns

Polref  x  Type_1           Type_2           Type_3
ABHX06HQ01 Escape Of Water
ABHX06HQ01                  Escape Of Water
ABHX06HQ01                                   Escape Of Water

And adding a group by and aggregation function like so:

-- Remove additional rows.
SELECT
    PolRef,
    MAX(CASE WHEN Number = 1 THEN [TYPE_ID] ELSE '' END) AS Type_1,
    MAX(CASE WHEN Number = 2 THEN [TYPE_ID] ELSE '' END) AS Type_2,
    MAX(CASE WHEN Number = 3 THEN [TYPE_ID] ELSE '' END) AS Type_3
FROM
    YourTable
GROUP BY
    PolRef
;

Reduces the number of rows returned, from three to one.

Polref  x  Type_1           Type_2           Type_3
ABHX06HQ01 Escape Of Water  Escape Of Water  Escape Of Water

answered Dec 13, 2016 at 13:35

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lynchie Over a year ago

Top man, I had started doing this using UNION's and ended up with a hideous looking view in the end, this has just condensed it all down. Thank you for a very precise and on point answer.

David Rushton Over a year ago

Not a problem. Glad it helped. I find this approach quicker to code than using the pivot operator. I often use Excel formulas to generate the case expressions for me (I refuse to re-type the same code over and over again!).

Lynchie Over a year ago

I agree - I get myself all in a mix up with PIVOT's and I hear that on the Excel Expression front - exactly what I did lol

Collectives™ on Stack Overflow

SQL PIVOT - Multiple Dynamic Columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related