Convert Rows to columns using 'Pivot' in SQL | Part - 15

We will learn how to use the SQL Server PIVOT operator to convert rows to columns.

Pivot table Syntax:
SELECT
[non-pivoted column], -- optional
[additional non-pivoted columns], -- optional
[first pivoted column],
[additional pivoted columns]
FROM (
SELECT query producing sql data for pivot
-- select pivot columns as dimensions and
-- value columns as measures from sql tables
) AS TableAlias
PIVOT
(
aggregation function (column for aggregation or measure column) -- MIN,MAX,SUM,etc
FOR [column name containing values for pivot table columns]
IN (
[first pivoted column], ..., [last pivoted column]
)
) AS PivotTableAlias
ORDER BY clause -- optional

Suppose a table which contain each product have sales, need to evaluate product wise total sales in a row:
Table name: sales.SalesOrderDetail

1)select * from sales.SalesOrderDetail

2)select ProductID,sum(LineTotal) Total_Price from sales.SalesOrderDetail group by productid order by ProductID

3) using pivot with static columns:

select * from (
select ProductID,LineTotal from sales.SalesOrderDetail) source
pivot( sum(LineTotal) for ProductID in([707],[708],[709])) piv

3) Dynamic Pivot Table Queries in SQL:

Find the year wise dues in each region
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(Name as varchar) + ']',
'[' + cast(Name as varchar)+ ']'
)
FROM Sales.SalesTerritory
print @PivotColumnHeaders

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
YEAR(H.OrderDate) [Year],
T.Name,
H.TotalDue
FROM Sales.SalesOrderHeader H
LEFT JOIN Sales.SalesTerritory T
ON H.TerritoryID = T.TerritoryID
) AS PivotData
PIVOT (
SUM(TotalDue)
FOR Name IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'