I have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() , Aggregate functions in sql server and Identity column in sql server. Today I am going to explain regarding pivot and unpivot in Sql Server.

Pivot is used to change the table tabular form. This means that you can change the output of the existing table in a form where one of your column becomes your row and rows becomes your columns. Suppose I have a one sales table with data from three different countries. Now my requirement is to show this table in a different way in which I want to make this all countries as a column. I will explain it in more easier way with example but lets first go with syntax of pivot table.

SELECT column_name(s), pivot_column_name(s)
FROM table_name    
PIVOT
(
    <aggregation function>(column_name) 
FOR [column on which you want to apply pivot]
IN  (list of the columns for pivot)
) AS [alias]
<optional ORDER BY clause>;

Now lets take an example. Below is my sales table with records for four products and three countries.

CREATE TABLE SALES
(
	PRODUCT VARCHAR(100) NOT NULL,
	PRICE DECIMAL(18,3) NOT NULL,
	COUNTRY VARCHAR(20) NOT NULL
)

INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('IPHONE 5S',600.79,'USA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('HTC ONE X',400.34,'USA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('BLACKBERRY Z10',700.74,'USA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('BLACKBERRY Z10',700.74,'USA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('SONY XPERIA M',200.65,'USA')

INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('IPHONE 5S',700.25,'INDIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('HTC ONE X',480.42,'INDIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('IPHONE 5S',700.68,'INDIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('SONY XPERIA M',280.76,'INDIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('BLACKBERRY Z10',800.74,'INDIA')

INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('IPHONE 5S',635.45,'AUSTRALIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('HTC ONE X',490.32,'AUSTRALIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('IPHONE 5S',635.45,'AUSTRALIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('SONY XPERIA M',220.76,'AUSTRALIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('SONY XPERIA M',220.76,'AUSTRALIA')
INSERT INTO SALES(PRODUCT,PRICE,COUNTRY) VALUES('BLACKBERRY Z10',690.74,'AUSTRALIA')

Now in the above table I want use country wise pivot where my country row values will become columns. So my query will be like below.

SELECT * FROM SALES
PIVOT (AVG (PRICE)  FOR COUNTRY IN ([AUSTRALIA],[INDIA],[USA])) AS AVGPRODUCTPRICE

Now take a different approach where I want to pivot my table based on the product. Below query will pivot my table based on products.

SELECT * FROM @SALES
PIVOT (AVG (PRICE) FOR PRODUCT IN ([IPHONE 5S],[HTC ONE X],[SONY XPERIA M],[BLACKBERRY Z10])) AS AVGPRODUCTPRICE

You can use convert and cast like functions for appropriate result where you want to change the data type of your pivoted columns. Take a look at below query.

SELECT PRODUCT,CAST([AUSTRALIA] AS DECIMAL(18,2)) AS [AUSTRALIA],CAST([INDIA] AS DECIMAL(18,2)) AS [INDIA],
CAST([USA] AS DECIMAL(18,2)) AS [USA] FROM @SALES
PIVOT (AVG (PRICE)  FOR COUNTRY IN ([AUSTRALIA],[INDIA],[USA])) AS AVGPRODUCTPRICE

Hope this will help you to understand Pivot.

Visit my sqlfiddle for above example : http://sqlfiddle.com/#!3/4310a/1

Now with the UNPIVOT you can consider the same thing with reverse engineering. So in UNPIVOT scenario is all about to make my column in rows. Here I am not going to describe the whole UNPIVOT as just a simple example is fair enough to understand the whole thing.

Now once again I have a simple sales table with different approach where I have a month wise columns for total sales. each row contains the year and month wise total sales. Below is a simple sales table.

CREATE TABLE SALES
(
	YEAR INT NOT NULL,
	January INT NOT NULL,
	February INT NOT NULL,
	March INT NOT NULL,
	April INT NOT NULL,
	May INT NOT NULL,
	June INT NOT NULL,
	July INT NOT NULL,
	August INT NOT NULL,
	September INT NOT NULL,
	October INT NOT NULL,
	November INT NOT NULL,
	December INT NOT NULL
)

INSERT INTO SALES(YEAR,January,February,March,April,May,June,July,August,September,October,November,December)
			VALUES(2010,1000,1055,1098,2013,1050,1254,1876,999,1234,1274,1987,1651)

INSERT INTO SALES(YEAR,January,February,March,April,May,June,July,August,September,October,November,December)
			VALUES(2011,2002,2671,2657,2013,1097,2309,2576,2209,2675,2678,2987,2225)

INSERT INTO SALES(YEAR,January,February,March,April,May,June,July,August,September,October,November,December)
			VALUES(2012,3912,3781,3765,3567,3119,3324,3567,3465,3987,1290,1106,3535)

Now I want a result where i just need three columns Year, Sales, YearMonth. For this result we just need below simple query to get the result.

SELECT YEAR,SALES,YEARMONTH FROM SALES
UNPIVOT (SALES FOR YEARMONTH  IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])) AS MONTH

Visit my sqlfiddle for above example : http://sqlfiddle.com/#!3/96fe5/3