Reading a narrow pivot table to create a new wide dynamic pivot table using the values from a field of narrow table in sql server. (SQL Server skill level: Advanced/Expert)
Example:
Narrow table.
Docnum description amt
100 subtotal 100.00
100 tax 7.00
100 freight 3.5
200 subtotal 50.00
200 tax 3.5
200 giftwrap 2.00
300 subtotal 25.00
Convert to wide table.
Docnum subtotal tax freight giftwrap
100 100.00 7.00 3.50 0.00
200 50.00 3.5 0.00 2.00
300 25.00 0.0 0.0 0.0
- Populate the narrow table with data.
- Get unique values of the description field into a temporary table.
- Then in while loop populate the values in the temporary table to declared variable.
- Then build the new table with the select following select statement.
select doc, @variable, amt into wide from narrow where 1=2 - Then in while loop update the fields based on the unique values in the temporary table.