--- title: Dynamic Pivot SQL description: Learn how to dynamically pivot SQL data with a stored procedure, avoiding the hassle of manually naming field values. Get the code and examples in this informative post. image: https://booking.datalabsolutions.ai/hubfs/dynamic-pyvot.jpg --- [Skip to content](https://booking.datalabsolutions.ai/blog/dynamic-pivot-sql#main-content) [![Wordmark_Dark_500px](https://booking.datalabsolutions.ai/hs-fs/hubfs/Wordmark_Dark_500px.png?width=148&height=40&name=Wordmark_Dark_500px.png "Wordmark_Dark_500px")](http://www.datalabsolutions.ai) - [About](https://booking.datalabsolutions.ai/about) - [Services](https://booking.datalabsolutions.ai/services) - [Resources](https://booking.datalabsolutions.ai/blog) - [Technology Partners](https://booking.datalabsolutions.ai/technology-partners) - [Contact Us](https://booking.datalabsolutions.ai/contact) - [About](https://booking.datalabsolutions.ai/about) - [Services](https://booking.datalabsolutions.ai/services) - [Resources](https://booking.datalabsolutions.ai/blog) - [Technology Partners](https://booking.datalabsolutions.ai/technology-partners) - [Contact Us](https://booking.datalabsolutions.ai/contact) [Contact Us](https://booking.datalabsolutions.ai/contact) [Guides](https://booking.datalabsolutions.ai/blog/tag/guides) # Dynamic Pivot SQL Datalab ![](https://booking.datalabsolutions.ai/hubfs/dynamic-pyvot.jpg) One of the frustrating things about using the SQL Server PIVOT function is that you need to explicitly name the field values that you want to be pivoted across the horizontal access of your result set. The problem with this is that this list of values could change over time as new data is added to the database. So what's the solution? In this post I’ll demonstrate how you can create a stored proc that will take a SQL query, derive the unique values in a column and dynamically create the SQL necessary to generate PIVOT table based on these values. #### The Solution Below you’ll see the code needed to create a stored procedure that takes a normal select query and automatically determines the column headings for the PIVOT statement. Let me take you through the code: @SQL = This is a variable that will hold a normal select statement. @Columns = This denotes the list of columns that you want to display along the vertical axis. @PivotCol = This is the name of the column that you want to pivot across the horizontal axis. @Summaries = This is the aggregate function and field that you want to summaries in the pivot table e.g. SUM(SalesAmount) @Debug = 0 = Run the script, 1 = Debug the code, in this instance the SQL that is used to create the pivoted result set is printed to the results pane in SSMS. The first thing we have to determine is the distinct field values that exists within the column that you have chosen to be the @PivotCol. In order to do this we create a dynamic query that is placed inside a vairable @IDListSQL. The query uses the @SQL query and the XML Path function to return a concatenated list of values, we place these in square brackets just in case some of the values have invalid characters for column names i.e. they start with a number. In order to make the code more readable I’ve used the replace function to replace place holders in the query with the values from the actual variable. I find this helps make the code more readable and maintainable going forward, there is no need for lots of string concatenation. I then use the sp\_executesql system stored proc to execute the dynamic sql and return the unique list of column values into the @IDList varaible, this list will eventually make up our horizontal axis. Once we have a list of fields we then construct the final select statement. Again I uses the replace function to switch place holders in the dynamic sql statement with the values in the actual variables. Once this is done we can execute the SQL and generate a pivoted data set.   #### Examples  Here’s an example of the proc in action.   You can see in this example that we've used a Row Construcutor to create our sample data. We demonstrated the various uses of Row Constructors in a previous blog post. ####  Downloads SQL Script: [Download ](https://booking.datalabsolutions.ai/hubfs/datalab%20-%20Quick%20Tip%20-%20Dynamic%20Pivot%20SQL.zip)   [![Share on Facebook](https://static.hubspot.com/final/img/common/icons/social/facebook-24x24.png)](http://www.facebook.com/share.php?u=https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Dfacebook) [![Share on LinkedIn](https://static.hubspot.com/final/img/common/icons/social/linkedin-24x24.png)](http://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Dlinkedin) [![Share on Twitter](https://static.hubspot.com/final/img/common/icons/social/twitter-24x24.png)](https://twitter.com/intent/tweet?original_referer=https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&url=https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&source=tweetbutton&text=Dynamic%20Pivot%20SQL) [![Share on Pinterest](https://static.hubspot.com/final/img/common/icons/social/pinterest-24x24.png)](http://pinterest.com/pin/create/button/?url=https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Dpinterest&media=http%3A%2F%2Fcdn1.hubspot.com%2Fhub%2F158015%2F305390_10100548508246879_837195_59275782_6882128_n.jpg) [![Share on Email](https://static.hubspot.com/final/img/common/icons/social/email-24x24.png)](mailto:?subject=Check%20out%20https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Demail%20&body=Check%20out%20https%3A%2F%2Fbooking.datalabsolutions.ai%2Fblog%2Fdynamic-pivot-sql%3Futm_medium%3Dsocial%26utm_source%3Demail) ![](https://booking.datalabsolutions.ai/hubfs/4.%20LinkedIn-Logo-286px-1.1.png) # Datalab [info@datalab.co.uk](mailto:info@datalab.co.uk) #### We are a data analytics and business intelligence cosnultancy operating from Cape Town. We offer consulting and data solution implementation services ranging from small scale to enterprise grade ## Related resources [![](https://booking.datalabsolutions.ai/hs-fs/hubfs/Blog%20Post%20-%20%20Finance%20-%20Case%20Study%20-%20Mukuru.png?width=352&name=Blog%20Post%20-%20%20Finance%20-%20Case%20Study%20-%20Mukuru.png)](https://booking.datalabsolutions.ai/blog/mukuru-a-modern-data-stack-implementation) ### [Mukuru - Modern Data Stack Implementation - Case Study](https://booking.datalabsolutions.ai/blog/mukuru-a-modern-data-stack-implementation) [![](https://booking.datalabsolutions.ai/hs-fs/hubfs/Blog%20Post%20-%20Not%20For%20Profit-%20Case%20Study%20-%20The%20Reading%20Agency.png?width=352&name=Blog%20Post%20-%20Not%20For%20Profit-%20Case%20Study%20-%20The%20Reading%20Agency.png)](https://booking.datalabsolutions.ai/blog/reading-agency-transforming-campaign-reporting) ### [Reading Agency - Transforming Campaign Reporting - Case Study](https://booking.datalabsolutions.ai/blog/reading-agency-transforming-campaign-reporting) [![](https://booking.datalabsolutions.ai/hubfs/Blog%20Post%20-%20Food%20%26%20Beverage%20%20-%20Case%20Study%20-%20FreshGround.svg)](https://booking.datalabsolutions.ai/blog/freshground-driving-data-accessibility) ### [FreshGround - Driving Data Accessibility - Case Study](https://booking.datalabsolutions.ai/blog/freshground-driving-data-accessibility) ![Coworkers sitting together and smiling outside.](https://booking.datalabsolutions.ai/hs-fs/hubfs/raw_assets/public/datalab/images/datalab-logo-white.png?width=149&height=40&name=datalab-logo-white.png "Coworkers sitting together and smiling outside.") We know the data challenges your organisation face, and have the team and expertise to help. [Follow us on Facebook](https://www.facebook.com) [Follow us on LinkedIn](https://www.linkedin.com) [Follow us on Twitter](https://www.twitter.com) [Follow us on Instagram](https://www.instagram.com) ##### Company - [About](https://booking.datalabsolutions.ai/about) - [Services](https://booking.datalabsolutions.ai/services) - [Resources](https://booking.datalabsolutions.ai/blog) - [Technology Partners](https://booking.datalabsolutions.ai/technology-partners) - [Contact Us](https://booking.datalabsolutions.ai/contact) - [About](https://booking.datalabsolutions.ai/about) - [Services](https://booking.datalabsolutions.ai/services) - [Resources](https://booking.datalabsolutions.ai/blog) - [Technology Partners](https://booking.datalabsolutions.ai/technology-partners) - [Contact Us](https://booking.datalabsolutions.ai/contact) ##### Services - [Data Strategy](https://booking.datalabsolutions.ai/services#datastrategy) - [Cloud Architecture](https://booking.datalabsolutions.ai/services#cloudarchitecture) - [Data Integration](https://booking.datalabsolutions.ai/services#dataintegration) - [Data Visualisation](https://booking.datalabsolutions.ai/services#datavisualisation) - [Embedded Analytics](https://booking.datalabsolutions.ai/services#datamonetisation) - [Capability Development](https://booking.datalabsolutions.ai/services#capabilitydevelopment) - [Data Strategy](https://booking.datalabsolutions.ai/services#datastrategy) - [Cloud Architecture](https://booking.datalabsolutions.ai/services#cloudarchitecture) - [Data Integration](https://booking.datalabsolutions.ai/services#dataintegration) - [Data Visualisation](https://booking.datalabsolutions.ai/services#datavisualisation) - [Embedded Analytics](https://booking.datalabsolutions.ai/services#datamonetisation) - [Capability Development](https://booking.datalabsolutions.ai/services#capabilitydevelopment) ##### Join Our Newsletter Copyright © 2026 Datalab 2023 All rights reserved. ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Datalab", "url" : "https://booking.datalabsolutions.ai/blog/author/datalab" }, "dateModified" : "2024-04-26T09:34:15.688Z", "datePublished" : "2024-04-26T09:22:39.000Z", "headline" : "Dynamic Pivot SQL", "image" : [ "https://booking.datalabsolutions.ai/hubfs/dynamic-pyvot.jpg" ], "mainEntityOfPage" : { "@id" : "https://booking.datalabsolutions.ai/blog/dynamic-pivot-sql", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://booking.datalabsolutions.ai/hubfs/Wordmark_Dark_500px_no_bg.png" }, "name" : "datalab" } } ```