Usage of Pivot () operator 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 <non-pivoted column>, 
    [first pivoted column] AS <column name>, 
    [second pivoted column] AS <column name>, 
    ... 
    [last pivoted column] AS <column name> 
FROM 
    (<SELECT query that produces the data>)  
    AS <alias for the source query> 
PIVOT 
( 
    <aggregation function>(<column being aggregated>) 
FOR  
[<column that contains the values that will become column headers>]  
    IN ( [first pivoted column], [second pivoted column], 
    ... [last pivoted column]) 
) AS <alias for the pivot table> 
<optional ORDER BY clause>;
------------
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:

Use ALTER COLUMN statement with a default() mask to apply data masking

Here is what I am going to do.

Here is a query that retrieves FirstName, LastName, City from Employees table in the Northwind database on my SQL Server 2016 SP2 named instance OHANA.

Use Northwind
Go
SELECT FirstName, LastName, City
FROM Employees

Now I will apply a datamask to the FirstName column with a default() datamask using the following Alter Table, Alter Column statements.

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

Use Northwind
Go
Alter table Employees
Alter Column FirstName nvarchar(10) Masked with (Function='default()')

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

When I run the above query, the query runs without errors. whenever I run the query, the data is not masked for me, because I have permissions.

To test the masking I create a user, hacker3 as follows:

--

/* create a hacker3 user*/

CREATE USER hacker3 WITHOUT LOGIN;
GRANT SELECT ON EMPLOYEES TO hacker3;
EXECUTE AS USER='hacker3'

----

DataMask_01

 Now I run a SELECT query, the same one we used earlier and get this response.

DataMask_02

You will observe that the FirstName column is masked. Note that the FirstNames in the table ranges from having 4 to 8 letters but the default() masking function replaces them with 3 xxx's.

Note that if you run a new query, you are not running as hacker3.

I have used the 'hacker' a couple of times while testing.