This is a typical pivot transformation, and conditional aggregation, as [suggested by Phil][1], is the good old way of implementing it.

There is also a more modern syntax of achieving the same result, which uses the PIVOT clause:

    SELECT
      CompanyName,
      TotalOpenClaims     = [1],
      TotalClosedClaims   = [2],
      TotalReOpenedClaims = [3],
      TotalPendingClaims  = [4]
    FROM
      dbo.Claims
      PIVOT
      (
        COUNT(ClaimID)
        FOR StatusID IN ([1], [2], [3], [4])
      ) AS p
    ;

Internally this arguably simpler looking syntax is equivalent to Phil's `GROUP BY` query. More exactly, it is equivalent to this variation:

    SELECT
      CompanyName,
      TotalOpenClaims     = COUNT(CASE WHEN StatusID = 1 THEN ClaimID END),
      TotalClosedClaims   = COUNT(CASE WHEN StatusID = 2 THEN ClaimID END),
      TotalReOpenedClaims = COUNT(CASE WHEN StatusID = 3 THEN ClaimID END),
      TotalPendingClaims  = COUNT(CASE WHEN StatusID = 4 THEN ClaimID END)
    FROM
      dbo.Claims
    GROUP BY
      CompanyName
    ;

So, a `PIVOT` query is an implicit `GROUP BY` query, essentially.

`PIVOT` queries, however, are notoriously trickier in handling than explicit `GROUP BY` queries with conditional aggregation. When you are using `PIVOT`, you need to always keep in mind this one thing:

* **All columns** of the dataset being pivoted (`Claims` in this case) that are not explicitly mentioned in the PIVOT clause **are GROUP BY columns**.

If `Claims` consists of only the three columns shown in your example, the `PIVOT` query above will work as expected, because apparently `CompanyName` is the only column not explicitly mentioned in `PIVOT` and thus ends up as the only criterion of the implicit `GROUP BY`.

However, if `Claims` has other columns (say, `ClaimDate`), they will implicitly be used as additional `GROUP BY` columns – that is, your query will essentially be doing

    GROUP BY CompanyName, ClaimDate, ... /* whatever other columns there are*/`

The result will most likely be not what you want.

That is easy to fix, though. In order to exclude irrelevant columns from participating in the implicit grouping, you can just use a derived table, where you will select only the columns needed for the result, although that makes the query less elegant-looking:

```
SELECT
  CompanyName,
  TotalOpenClaims     = [1],
  TotalClosedClaims   = [2],
  TotalReOpenedClaims = [3],
  TotalPendingClaims  = [4]
FROM
  (SELECT ClaimID, CompanyName, StatusID FROM dbo.Claims) AS derived
  PIVOT
  (
    COUNT(ClaimID)
    FOR StatusID IN ([1], [2], [3], [4])
  ) AS p
;
```

Still, if `Claims` is already a derived table, there is no need to add another level of nesting, just make sure that in the current derived table you are selecting only the columns required to produce the output.

You can read more about `PIVOT` in the manual:

* [Using PIVOT and UNPIVOT][2]

  [1]: https://topanswers.xyz/databases?q=1216#a1442
  [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

It's easiest with `SUM()` and a `CASE` statement:

    select CompanyName, 
    sum(case when StatusID=1 then 1 else 0 end) as TotalOpenClaims,
    sum(case when StatusID=2 then 1 else 0 end) as TotalClosedClaims,
    sum(case when StatusID=3 then 1 else 0 end) as TotalReOpenedClaims,
    sum(case when StatusID=4 then 1 else 0 end) as TotalPendingClaims
    from Claims
    group by CompanyName;