CODEWITHSUNDEEP
sql-servert-sqlpivot
Lynchie
Lynchie

Reputation: 1149

SQL PIVOT - Multiple Dynamic Columns

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.

Upvotes: 0

Views: 73

Answers (1)

David Rushton
David Rushton

Reputation: 5030

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

Upvotes: 1

Related Questions