Tutorial by Examples

Using Item Sales Table from Example Database, let us calculate and show the total Quantity we sold of each Product. This can be easily done with a group by, but lets assume we to 'rotate' our result table in a way that for each Product Id we have a column. SELECT [100], [145] FROM (SELECT ItemI...

Below is a simple example which shows average item's price of each item per weekday. First, suppose we have a table which keeps daily records of all items' prices. CREATE TABLE tbl_stock(item NVARCHAR(10), weekday NVARCHAR(10), price INT); INSERT INTO tbl_stock VALUES ('Item1', 'Mon', 110), (...

One problem with the PIVOT query is that you have to specify all values inside the IN selection if you want to see them as columns. A quick way to circumvent this problem is to create a dynamic IN selection making your PIVOT dynamic. For demonstration we will use a table Books in a Bookstore’s dat...