How do you use pivot() oprator in SQL Server?

Pivot() is a relational operator that changes a table-valued expression into another table. It rotates the table-valued expression by turning the unique values from one column  in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that are wanted in the final output.

This is the syntax from MSDN for the PIVOT operator.

------------

SELECT , 
    [first pivoted column] AS , 
    [second pivoted column] AS , 
    ... 
    [last pivoted column] AS  
FROM 
    ()  
    AS  
PIVOT 
( 
    () 
FOR  
[]  
    IN ( [first pivoted column], [second pivoted column], 
    ... [last pivoted column]) 
) AS  
;

------------

Let us take an example frrom Northwind database. Here is a query that Selects lastname of employee and Unitprice from the Order Details table for UnitPrice greater than 50 and Quantity>10.

Pivot_0.png

You can see that Employees figure in many orders (have order details) with different UnitPrices. Now if you want to aggreegate the average Unitprice of articles sold by each employee (or a chosen number of employees) you need to do an aggregate.

For the above query we cannot directly use the PIVOT operator and we need to create an ALIAS as shown. PriceTable is the ALIAS for this query

----------

Select * FROM
(SELECT        Employees.LastName, [Order Details].UnitPrice
FROM            Employees INNER JOIN
                         Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                         Products ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].UnitPrice >50.00 and [Order Details].Quantity>10)
as PriceTable

-----------

Nothing is changed as far as the Query return is concerned but we now have an  ALIAS.

Now we create a table which aggregates the average of UnitPrice for some named Employees using their LastName from the PriceTable as shown here.

----------

Select * FROM
(SELECT        Employees.LastName, [Order Details].UnitPrice
FROM            Employees INNER JOIN
                         Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
                         [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                         Products ON [Order Details].ProductID = Products.ProductID
WHERE [Order Details].UnitPrice >50.00 and [Order Details].Quantity>10)
as PriceTable
Pivot(Avg(UnitPrice) For LastName in ([King], [Davolio], [Fuller],[Peacock],[Suyama]))
as StudentPivot

----------------

When you run this the response is a table that has the values we were looking for:

How do you find details about the aliases in WMI?

In order to explore with WMI we need to know the alias for the item we want to explore. Let us find out what the 'alias' in WMI is.

Let us get the help on 'alias' first.
Run this command (just type WMIC in search charm in Windows 10[this is where you see the Cortana icon]) and the C:\windows\System32\wbem\wmic.exe will come up as shown.

Now type alias /? at the wmic:root> prompt as shown.
-----------------------------------
wmic:root\cli>alias /?
ALIAS - Access to the aliases available on the local system
HINT: BNF for Alias usage.
( [WMIObject] | [] | [] ) [].
USAGE:
ALIAS ASSOC []
ALIAS CALL []
ALIAS CREATE
ALIAS DELETE
ALIAS GET [] []
ALIAS LIST [] []
wmic:root\cli>
------------------
Now we can get the aliases and what they represent using the Alias List command as shown in the next using the list switch - brief:
------------
wmic:root\cli>Alias List Brief
FriendlyName         PWhere                                  Target
NICConfig               Where Index=#                        Select * from Win32_NetworkAdapterConfiguration
SysDriver                 where Name='#'                       Select * from Win32_SystemDriver
TapeDrive                                                                 Select * from Win32_TapeDrive
NTEventLog           WHERE LogfileName='#'        Select * from Win32_NTEventlogFile
UserAccount                                                             Select * from Win32_UserAccount
Job                          WHERE jobid=#                               Select * from Win32_ScheduledJob
SoftwareElement                                                      Select * from Win32_SoftwareElement
--------------------------------------------------------
The full list is long and above is a clipped list.

Here is picture of the complete list (use a magnifier to see it):

WMicaliaslist.png