FOR clause in mssql2k5.

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, ChrisRIt 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.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:usyswa3eGHA.4932@.TK2MSFTNGP03.phx.gbl...
> 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
>|||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 :)
"ChrisR" wrote:

> Here is the BOL example for the PIVOT function:
> SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp
4,
> [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
>
>|||You can display row data to column using PIVOT and FOR clause .
"ChrisR"?? ??? ??:

> Here is the BOL example for the PIVOT function:
> SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp
4,
> [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
>
>