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)


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

  1. Populate the narrow table with data.
  2. Get unique values of the description field into a temporary table.
  3. Then in while loop populate the values in the temporary table to declared variable.
  4. Then build the new table with the select following select statement.
    select doc, @variable, amt into wide from narrow where 1=2
  5. Then in while loop update the fields based on the unique values in the temporary table.
Wait! Before You GoWould you like to talk to a business software expert to answer your questions?

When you schedule a free consultation with MIBAR, you will experience a one-on-one conversation with a business technology expert who is passionate about understanding your unique needs or issues. Schedule a free consultation today at the link below or give us a call at (212) 869-9300.

Learn More