Dynamic Pivot in SQL Server

Pivot in SQL Server is used to transform row values of a column as the columns of the output.

What is the need of dynamic pivot?
When the row values of the column on which you are going to pivot are static i.e you already know what all set of values exist in the column, then static pivot would work for you. The need of dynamic pivot arises when the row values for the column are not static i.e you do not know what all values would be there in the column

Example

CREATE TABLE #tblitems(
  item    VARCHAR(10),
  parameter  VARCHAR(10),
  value    INT
)

INSERT INTO #tblitems
SELECT 'item1', 'param1', 10 UNION ALL
SELECT 'item1', 'param2', 20 UNION ALL
SELECT 'item1', 'param3', 30 UNION ALL
SELECT 'item2', 'param1', 15 UNION ALL
SELECT 'item2', 'param2', 20

-- Creating a list of all distinct row values that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                     SELECT DISTINCT ',[' + parameter + ']'
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT item, ' + @paramList + ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(value) FOR parameter IN (' + @paramList + ')) pvt'

EXEC sp_executesql @query
DROP TABLE #tblitems

OUTPUT