Search code examples
sqlpivotinequality

Like PIVOT but values not discrete

A table lists multiple events where each event has four attributes (columns), call them A, B, C, P

It would be simpleto pivot to get a table with columns for A, B, C, P=1, P=2, P=3, etc.

However, I need the columns to be A, B, C, P<1, P<2, P<3, etc.

In other words, if a row of the "simple" way were X, Y, Z, 7, 3, 5, 2 then what I actually need is X, Y, Z, 7, 10, 15, 17 because any P less than N is also less than N+1.

I know I can compute the values (X, Y, Z, 7, 10, 15, 17), load them into a temporary table, and pivot that, but maybe there's something simple that my SQL talents don't recognize immediately?

If it matters, the result will end up in SSRS.

Solution

  • If I understand you correctly, then this should work.

    SELECT
    A,
    B,
    C,
    SUM(CASE WHEN P < 1 THEN 1 ELSE 0 END) AS P1,
    SUM(CASE WHEN P < 2 THEN 1 ELSE 0 END) AS P2,
    SUM(CASE WHEN P < 3 THEN 1 ELSE 0 END) AS P3,
    SUM(CASE WHEN P < 4 THEN 1 ELSE 0 END) AS P4
FROM
    Events
GROUP BY
    A,
    B,
    C

    This is not dynamic. For example, if you have a row in there with P=4 then it will not add a row for P<5. It's also using strictly < and not <=.