Using a 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 , 
    [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:

Historic meeting that may change the shape of things to come

The historic meeting and the follow up signing is nothing short of a miracle considering the events that preceded the meeting.

Ok, the meeting is over, the deal is done. What is the deal? It is heavily discussed in the media. It was surprising that at the time it was signed very little was known about the contents and the TV announcers were using words like 'something' was signed.

It appears the wording is still broad, and it is said to be 'Complete'. One must wait and see how it turns out. Our hopes are that it will result in lasting peace and scenes of Hiroshima, Chernobyl and Fukushima are just nightmares from which we woke up.

Here are some pictures taken on that day from a TV screen with a Microsoft Lumia phone. Watch the body language and the emotions on the faces.

 If you missed the signing, here it is.

The Signing

Borghese Museum Visit - Rome Part 3

This is the final part of our visit to Rome. With this we will have seen the major attractions of Rome. We left out hotel after the lunch and rest.

The visitors are scheduled with assigned time slots and ours was 5:00 PM to 7:00 PM. We could walk to Borghese from our hotel. It took perhaps 20 minutes. Our local guide in Rome met us at the entrance which is the center door at the façade of the gallery.

Galleria Borghese once called the 'Elysium of delights' is truly a delightful place, especially the vast and beautiful garden. The gallery houses paintings by Caravaggio, Raphael, Titian and others. The most outstanding sculptures are of Bernini, quite a few of them in one place. The Rape of Proserpina; Apollo and Daphne and David are more well-known sculptures of Bernini. One must go around and round these sculptures to appreciate these Baroque masterpieces from different angles as the related story slowly unfolds.

You will also find the beautiful sculpture of sister of Napoleon 1 reclining on a divan.

The garden surrounding the gallery is quite extensive which also includes the aviary and the secret gardens. We did not try to enter the secret gardens but the English style garden at the rear of the gallery was very nice despite the lack of flowers. We could see the lime bushes and citrus plants heavy with limes.

The Venus fountain placed central to the garden behind the gallery provided the perfect backdrop for the gallery.

YouTube Video has more details:

https://youtu.be/LcX0rhe9gf4

Here are some pictures of our visit to the Borghese.

Picture on the right : Bernini

We were quite tired and we wanted to try a different place to eat. we chose 44 Osteria which was right next to the hotel. As I entered I knew we made a mistake. There is very little vegetarian on the menu. Our Middle east server tried his level best to accommodate but it was not a place to eat. Since I was not having wine, the whole thing started looking ludicrous.  However, the restaurant had good reviews although quite pricey.

Source: Copied from TripAdvisor site

We continue our journey tomorrow to Orvieto in south western Umbria.