how can i get a result for the following query in a pivot form?

Question

Possible Duplicate:
Pivot data in T-SQL

please make needed changes in the query to create a pivot form display ...

select COUNT(employeeid) as count_of_employees,Title 
from HumanResources.Employee 
group by Title order by no_of_employees desc

this query is returning the following result

count_of_employees  Title
26                  Production Technician - WC50
26                  Production Technician - WC60
26                  Production Technician - WC40
25                  Production Technician - WC30
22                  Production Technician - WC20
17                  Production Technician - WC10
15                  Production Technician - WC45
14                  Sales Representative
9                   Buyer
5                   Marketing Specialist
4                   Scheduling Assistant

i need the desired result

Production Technician-WC50 |  Production Technician-WC60  |   Production Technician-WC40
           26              |            26                |                26

Answer 1

To pivot the rows into columns the way you described, you have to use the PIVOT table operator. Something like so:

SELECT *
FROM
(
  SELECT [count_of_employees], [Title]
  FROM YourQuery
  ) t
PIVOT
(
  MAX(count_of_employees)
  FOR Title IN([Production Technician - WC50], 
               [Production Technician - WC60], 
               [Production Technician - WC40],
               [Production Technician - WC30], 
               [Production Technician - WC20], 
               [Production Technician - WC10], 
               [Production Technician - WC45], 
               [Sales Representative], 
               [Buyer], 
               [Marketing Specialist], 
               [Scheduling Assistant])
 ) p;

SQL Fiddle Demo

---

But this is ugly, since you have to write these list of title. However, it is better to do this dynamically, by getting the list of titles dynamically like so:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +  QUOTENAME (title)
               FROM YourQuery
               FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')  ,1,1,'');

SET @query = 'SELECT ' + @cols + ' FROM
(
   SELECT [count_of_employees], [Title] FROM YourQuery
) t
PIVOT
(
   MAX(count_of_employees)
   FOR title IN (' + @cols + ')
             ) p ';

EXECUTE(@query);

SQL Fiddle Demo

This should give you:

| BUYER | MARKETING SPECIALIST | PRODUCTION TECHNICIAN - WC10 | PRODUCTION TECHNICIAN - WC20 | PRODUCTION TECHNICIAN - WC30 | PRODUCTION TECHNICIAN - WC40 | PRODUCTION TECHNICIAN - WC45 | PRODUCTION TECHNICIAN - WC50 | PRODUCTION TECHNICIAN - WC60 | SALES REPRESENTATIVE | SCHEDULING ASSISTANT |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     9 |                    5 |                           17 |                           22 |                           25 |                           26 |                           15 |                           26 |                           26 |                   14 |                    4 |

This is nicer, but you might need to be careful with dynamic SQL, reed this for more details:

• The Curse and Blessings of Dynamic SQL

---

Note that: the table YourQuery I used in my answer here, has to be replaced with your query:

select COUNT(employeeid) as count_of_employees,Title 
from HumanResources.Employee 
group by Title order by no_of_employees desc