Horizontal Count SQL

Question

I apologize if this is a duplicate question but I could not find my answer.

I am trying to take data that is horizontal, and get a count of how many times a specific number appears.

Example table

+-------+-------+-------+-------+
| Empid | KPI_A | KPI_B | KPI_C |
+-------+-------+-------+-------+
|   232 |     1 |     3 |     3 |
|   112 |     2 |     3 |     2 |
|   143 |     3 |     1 |     1 |
+-------+-------+-------+-------+

I need to see the following:

+-------+--------------+--------------+--------------+
| EmpID | (1's Scored) | (2's Scored) | (3's Scored) |
+-------+--------------+--------------+--------------+
|   232 |            1 |            0 |            2 |
|   112 |            0 |            2 |            1 |
|   143 |            2 |            0 |            1 |
+-------+--------------+--------------+--------------+

I hope that makes sense. Any help would be appreciated.

Is it **certain** that there will **always** be exactly three KPI_x columns? — user2338816, Jul 25 '14 at 02:08

score 2 · Answer 1 · answered Jul 24 '14 at 21:39

Since you are counting data across multiple columns, it might be easier to unpivot your KPI columns first, then count the scores.

You could use either the UNPIVOT function or CROSS APPLY to convert your KPI columns into multiple rows. The syntax would be similar to:

select EmpId, KPI, Val
from yourtable
cross apply
(
  select 'A', KPI_A union all
  select 'B', KPI_B union all
  select 'C', KPI_C
) c (KPI, Val)

See SQL Fiddle with Demo. This gets your multiple columns into multiple rows, which is then easier to work with:

| EMPID | KPI | VAL |
|-------|-----|-----|
|   232 |   A |   1 |
|   232 |   B |   3 |
|   232 |   C |   3 |
|   112 |   A |   2 |

Now you can easily count the number of 1's, 2's, and 3's that you have using an aggregate function with a CASE expression:

select EmpId,
  sum(case when val = 1 then 1 else 0 end) Score_1,
  sum(case when val = 2 then 1 else 0 end) Score_2,
  sum(case when val = 3 then 1 else 0 end) Score_3
from
(
  select EmpId, KPI, Val
  from yourtable
  cross apply
  (
    select 'A', KPI_A union all
    select 'B', KPI_B union all
    select 'C', KPI_C
  ) c (KPI, Val)
) d
group by EmpId;

See SQL Fiddle with Demo. This gives a final result of:

| EMPID | SCORE_1 | SCORE_2 | SCORE_3 |
|-------|---------|---------|---------|
|   112 |       0 |       2 |       1 |
|   143 |       2 |       0 |       1 |
|   232 |       1 |       0 |       2 |

Horizontal Count SQL

1 Answers1