BobbyJones BobbyJones - 5 months ago 10
SQL Question

SQL Converting Vertical Column to Horizontal and Counting Values

I really need your help and it seems from an SQL standpoint that I can't seem to quite think like an SQL programmer with trying to obtain the following result set.

Consider the following table below (which is a snapshot from my MS Access MDB file:

+-----------------+-----------------+-----------------+-----------------+
| Date Received | Request Type | Branch | Division |
+-----------------+-----------------+-----------------+-----------------+
| 2016-05-10 | Status Report | Alpha | AAA |
| 2016-05-11 | Business Case | Bravo | BB |
| 2016-05-12 | Business Plan | Charlie | C |
| 2016-05-13 | Project Charter | Alpha | A |
| 2016-05-14 | Status Report | Alpha | AAA |
| 2016-05-15 | Business Plan | Charlie | CC |
| 2016-05-16 | Financial Report| Alpha | AAAA |
| 2016-05-17 | Financial Report| Alpha | AA |
| 2016-05-18 | Status Report | Bravo | BBB |
| 2016-05-19 | Financial Report| Alpha | AAA |
| 2016-05-20 | Financial Report| Bravo | B |
+-----------------+-----------------+-----------------+-----------------+


I need your help to basically capture the metrics (occurrence/count of each request type and then sort it by Division), resulting in the new result set below:

+-----------------+-----------------+-----------------+-----------------+
| Division | Status Report | Business Case | Business Plan |
+-----------------+-----------------+-----------------+-----------------+
| A | 1 | 0 | 0 |
| AA | 1 | 0 | 0 |
| AAA | 1 | 0 | 0 |
| B | 0 | 1 | 0 |
| BB | 0 | 1 | 0 |
| BBB | 0 | 1 | 0 |
| C | 1 | 0 | 1 |
| CC | 1 | 0 | 1 |
| CCC | 1 | 0 | 1 |
+-----------------+-----------------+-----------------+-----------------+

Answer

Edit: count the request types for a branch:

DECLARE @count int;
DECLARE @count0 int;
DECLARE @count1 int;


set @count = (select Count(Request Type) from TABLENAME where Request Type = 'Status Report', Division = 'AAA')

set @count0 = (select Count(Request Type) from TABLENAME where Request Type = 'Business Case', Division = 'AAA')

set @count1 = (select Count(Request Type) from TABLENAME where Request Type = 'Business Plan', Division = 'AAA')

Edit: use this to order

order by COLUMNXXX ASC