0

Imagine the result of a query is something like the following:

+----+---------+--------+
| id | count   | type   |
+----+---------+--------+
|  1 | 20      | a      |
|  1 | 30      | b      |
|  1 | 10      | c      |
|  2 | 05      | a      |
|  2 | 20      | b      |
|  2 | 40      | c      |
+----+---------+--------+

and the expected result:

+----+---------+--------+------+
| id | a       | b      | c    |
+----+---------+--------+------+
|  1 | 20      | 30     | 10   |
|  2 | 05      | 20     | 40   |
+----+---------+--------+------+

I know some solutions which are complex using Cursor, Variables, Join and etc. I would like to find the most efficient one, otherwise I will handle it from the application layer.

Rick James
  • 135,179
  • 13
  • 127
  • 222
KOrrosh Sh
  • 134
  • 1
  • 8
  • I removed T-SQL based on a preponderance of evidence. You should tag only with the database you are using. – Gordon Linoff Nov 29 '16 at 17:04
  • Possible duplicate of [SQL Transpose Rows as Columns](http://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns) – CLAbeel Nov 29 '16 at 17:46

1 Answers1

4

One method uses conditional aggregation:

select id,
       sum(case when type = 'a' then count else 0 end) as a,
       sum(case when type = 'b' then count else 0 end) as b,
       sum(case when type = 'c' then count else 0 end) as c
from t
group by id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786