Here is the BOL example for the PIVOT function:

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;

What Im having a hard time understanding though is the FOR clause. Why is it
used instead of the WHERE clause?
TIA, ChrisR

It is not a WHERE clause, and I think they used a different keyword in order
to make that absolutely clear.  It only supports FOR <columnname> IN
(<values>).  If you need an actual WHERE clause, you can put one in the
derived table, or before the ORDER BY clause.


Show quoteHide quote

FOR clause is a part of the PIVOT table
you can say its all part of the FROM clause.

the result of this is actually a table on which you can do a select and a
filter

(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt


And the above piece of code means this
Do a count of purchase order id grouping by each of the employeeID given in
the inclause and replace the EmployeeID column with these columns
[164], [198], [223], [231], [233]  each having the split up count for its
employeeID.
I know I am confusing :)


Show quoteHide quote

You can display row data to column using PIVOT and FOR clause .

"ChrisR"님이 작성한 내용:

Show quoteHide quote

understanding what trigger is doing
Delete 'Non-Unique' Rows Question (with DDL)
Is this index redundant?
Different between ##table and #table regarding performance
Problems while migrating from MSSQL 2000 to 2005 - select... into fails in a trigger - please help.