how to format a table content

Question

I am new in database.I have execute a query like

SELECT   name                                       as request_name, 
         count(name)                                as no_of_open_req, 
         DATENAME(MM, Convert(DATE, created_date))  as month 
FROM     usm_request 
WHERE    DATENAME(YEAR, Convert(DATE,created_date)) = DATENAME(YEAR,GETDATE()) 
GROUP BY name, 
         DATENAME(YEAR, Convert(DATE, created_date)),
         DATENAME(MM, Convert(DATE,created_date)) 
ORDER BY DATENAME(MM, Convert(DATE, created_date)) 

and got the result

 request_name                no_of_open_req      month

Computer Request                   1             April
Desk Phone Request                 1             April
E-mail ID Creation Request         1             April
Computer Request                   19            February
Desk Phone Request                 12            February
Email ID Creation Request           8            February
Computer Request                    45           January
Desk Phone Request                  28           January
Email ID Creation Request           55           January
Computer Request                    18           March
Desk Phone Request                  24           March
E-mail ID Creation Request          35           March

But we need the result like

  request_name                  January   February    March    April

Computer Request                   45        19         18       1
Desk Phone Request                 28        12         24       1
E-mail ID Creation Request         55        8          35       1

Please help.

We have tried this query..

     SELECT * from (select name as [request_name],
                    count(name) as [no_of_open_req],
                    DATENAME(MM, Convert(DATE, created_date)) as [month] FROM usm_request WHERE DATENAME(YEAR,Convert(DATE,created_date))=DATENAME(YEAR,GETDATE()) group by name, DATENAME(YEAR, Convert(DATE, created_date)),DATENAME(MM, Convert(DATE,created_date)))as t PIVOT(sum(no_of_open_req) FOR month IN (['January'],['February'],['March'],['April'])) AS PivotTable

and we are getting this as a result. ALL NULL VALUES

          request_name              'January'   'February'  'March' 'April'
     Cell Phone Allocation             NULL         NULL      NULL    NULL
     Computer Request                  NULL         NULL      NULL    NULL
     Desk Phone Request                NULL         NULL      NULL    NULL
     Desk Phone Request test           NULL         NULL      NULL    NULL
     Email ID Creation Request         NULL         NULL      NULL    NULL
     E-mail ID Creation Request        NULL         NULL      NULL    NULL
     International Dialing Request     NULL         NULL      NULL    NULL
     New Employee Request              NULL         NULL      NULL    NULL
     New Non-Employee Request          NULL         NULL      NULL    NULL
     Onboard a Non-Employee            NULL         NULL      NULL    NULL
     Onboard a Non-Employee – Step 1   NULL         NULL      NULL    NULL
     Onboard a Non-Employee - Step 2   NULL         NULL      NULL    NULL

Thanks Monika

Answer 1

I think the problem is in the following part of your PIVOT statement:

FOR month IN (['January'],['February'],['March'],['April'])

Instead it should be

FOR month IN ([January],[February],[March],[April])

So, without the single-quotes. If you add those single quotes between the rectangular brackets, the statement will actually be looking for month values including the single quotes. For example, if you have ['January'] in your PIVOT list the statement will look for Month values of 'January'. Of course the values in the Month column do not have single-quotes in them.