Pivot a Table by month

Question

I am have trouble figuring out how to Pivot a table. I need an out put that looks like this:

 Month    Age_Group                    90%-100% 51%-89% 26%-50% <25%
 Jun-13   Early Childhood (3–5)         1   1   0   0
 Jun-13   Elementary (6–10)                 3   11  16  13
 Jun-13  Middle School (11–13)          3   3   5   14
 Jun-13   Teens (14–18)                 6   7   6   10
 Jun-13   Older Youth (19-21)           1   0   1   2

When the table pivots if there is a Null value that needs to change to 0. I have been looking at posts on here and I can't seem to wrap my head around the syntax to create the Pivot.

Here is the table:

CREATE TABLE Budget_Utilization (
    Month/YEAR varchar(7) ,
    Child_Count int, 
    Budget_Utilization  varchar(12),
    Age_Group varchar (20)     )

INSERT INTO  [GW_PPP].[dbo].[WSP_services_Counts] VALUES 
('Jun-13',  1,  '90% and Above',    'Early Childhood (ages 3–5)'),
('Jun-13',  3,  '90% and Above',    'Elementary (6–10)'),
('Jun-13',  3,  '90% and Above',    'Middle School/Pre-Teen (11–13)'),
('Jun-13',  1,  '90% and Above',    'Older Youth (19-21)'),
('Jun-13',  6,  '90% and Above',    'Teens (14–18)'),
('Jun-13',  1,  '51%-89%',  'Early Childhood (ages 3–5)'),
('Jun-13',  11, '51%-89%',  'Elementary (6–10)'),
('Jun-13',  3,  '51%-89%',  'Middle School/Pre-Teen (11–13)'),
('Jun-13',  7,  '51%-89%',  'Teens (14–18)'),
('Jun-13',  16, '26%-50%',  'Elementary (6–10)'),
('Jun-13',  5,  '26%-50%',  'Middle School/Pre-Teen (11–13)'),
('Jun-13',  1,  '26%-50%',  'Older Youth (19-21)'),
('Jun-13',  6,  '26%-50%',  'Teens (14–18)'),
('Jun-13',  13, '<25%', 'Elementary (6–10)'),
('Jun-13',  14, '<25%', 'Middle School/Pre-Teen (11–13)'),
('Jun-13',  2,  '<25%', 'Older Youth (19-21)'),
('Jun-13',  10, '<25%', 'Teens (14–18)')










  select [Month/YEAR],
  [Age_Group],
  coalesce([90% and Above], 0) [90% and Above],
  coalesce([51%-89%], 0) [51%-89%],
  coalesce([26%-50%], 0) [26%-50%],
  coalesce([<25%], 0) [<25%]
from
(
  select [Month/YEAR], Child_Count, Budget_Utilization,
   [Age_Group]
  from  [GW_PPP].[dbo].[WSP_services_Counts]
) d
pivot
(
  sum(Child_Count)
  for   [GW_PPP].[dbo].[WSP_services_Counts] in ([90% and Above], 
                             [51%-89%],
                             [26%-50%],
                             [<25%])
) piv

Answer 1

The basic syntax for a pivot with your current table will be the following. Then in order to replace the null in the final select with a zero, you can use coalesce:

select [month/year],
  age_group,
  coalesce([90% and Above], 0) [90% and Above],
  coalesce([51%-89%], 0) [51%-89%],
  coalesce([26%-50%], 0) [26%-50%],
  coalesce([<25%], 0) [<25%]
from
(
  select [month/year], child_count, budget_utilization,
    age_group
  from Budget_Utilization
) d
pivot
(
  sum(Child_Count)
  for Budget_Utilization in ([90% and Above], 
                             [51%-89%],
                             [26%-50%],
                             [<25%])
) piv;

See SQL Fiddle with Demo

Using your table name the query will be:

select [Month/YEAR],
  [Age_Group],
  coalesce([90% and Above], 0) [90% and Above],
  coalesce([51%-89%], 0) [51%-89%],
  coalesce([26%-50%], 0) [26%-50%],
  coalesce([<25%], 0) [<25%]
from
(
  select [Month/YEAR], Child_Count, Budget_Utilization,
   [Age_Group]
  from  [GW_PPP].[dbo].[WSP_services_Counts]
) d
pivot
(
  sum(Child_Count)
  for Budget_Utilization in ([90% and Above], 
                             [51%-89%],
                             [26%-50%],
                             [<25%])
) piv

Answer 2

If it is always going to be just those four categories, I would use a series of CASE statements instead of a pivot. see the SQLFiddle

SELECT  BU.MONTH_YEAR
        ,BU.AGE_GROUP
        ,SUM(CASE WHEN BU.BUDGET_UTILIZATION = '90% AND ABOVE' THEN BU.CHILD_COUNT ELSE 0 END) AS '90% AND ABOVE'
        ,SUM(CASE WHEN BU.BUDGET_UTILIZATION = '51%-89%' THEN BU.CHILD_COUNT ELSE 0 END) AS '51%-89%'
        ,SUM(CASE WHEN BU.BUDGET_UTILIZATION = '26%-50%' THEN BU.CHILD_COUNT ELSE 0 END) AS '26%-50%'
        ,SUM(CASE WHEN BU.BUDGET_UTILIZATION = '<25%' THEN BU.CHILD_COUNT ELSE 0 END) AS '<25%'
FROM BUDGET_UTILIZATION BU
GROUP BY
        BU.MONTH_YEAR
        ,BU.AGE_GROUP