SQL Server PIVOT Dynamic Columns

SQL Server PIVOT Dynamic Columns is a SQL statement in the SQL Server Specific category. When PIVOT column values are unknown, use dynamic SQL to construct the column list at runtime. The syntax is -- Dynamic PIVOT requires building the column list with dynamic SQL.. It returns dynamic pivot result. A typical example: DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); -- Get distinct values for pivot columns: SELECT @columns = STRING_AGG(QUOTENAME(department), ',') FROM (SELECT DISTINCT department FROM employees) AS depts; -- Build dynamic pivot query: SET @sql = N' SELECT * FROM ( SELECT department, job_title, salary FROM employees ) src PIVOT ( AVG(salary) FOR department IN (' + @columns + N') ) pvt' EXEC sp_executesql @sql; -- Automatically pivots on all departments -- Handles… A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.