declare @Departments table
(DepartmentID int identity(1,1),Name varchar(30));
insert into @Departments values('D1');
insert into @Departments values('D2');
insert into @Departments values('D3');
insert into @Departments values('D4');

declare @Employees table
(EmployeeID int identity(1,1),[First Name] varchar(20),[Last Name] varchar(20),DepartmentID int);
insert into @Employees values('John','Smith',1);
insert into @Employees values('Dave','Johnson',2);
insert into @Employees values('Mark','Andrew',2);
insert into @Employees values('George','Michael',4);
insert into @Employees values('Michael','Peterson',3);

select DepartmentName,NoOfEmployee
from
(select count(t1.EmployeeID) as NoOfEmployee, t2.Name as DepartmentName
from @Employees t1 inner join @Departments t2
on t1.DepartmentID=t2.DepartmentID
group by t1.DepartmentID,t2.Name)t1 where NoOfEmployee < 10

Output
DepartmentName | NoOfEmployee
---------------------
D1	      1
D2	      2
D3	      1
D4	      1

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

SELECT VendorID, EmployeeID, COUNT(PurchaseOrderID)
FROM Purchasing.PurchaseOrderHeader 
GROUP BY VendorID, EmployeeID

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt) p
UNPIVOT
    (Orders FOR Employee IN 
        (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
DROP TABLE pvt
GO

quote:

---

Originally posted by Delinda

Let's say, i've as follow,
....
How to use the SQL using PIVOT, and SQL using UNPIVOT based on result set above?

---