Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Fernando Ancona Camara
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
      • Trevor Momanyi

SQL Server using PIVOT with dynamic SQL example

SQL Server using a complex PIVOT function in a real world type scenario, where the number of columns may not always be the same.
Using Picot with Dynamic SQL

In the last article, I wrote about using PIVOT in a standard scenario where you know all of the columns that you want, using the same data we can now build on that to produce column names dependent on the actual data set. 

I will skip the first part of that article but provide the SQL to get us up and running here. First, lets create a data set.

More: SQL Server using PIVOT example
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row ID
MyCol NVARCHAR(10),--Value to become column
CalcCol INT--Value to be Summed
)
GO
DECLARE
 @Piv c1bs_Piv
INSERT INTO @Piv
SELECT '1','1',1 UNION
SELECT
 '1','s',1 UNION
SELECT
 '1','3',1 UNION
SELECT
 '2','2',6 UNION
SELECT
 '2','2',1 UNION
SELECT
 '2','3',1 UNION
SELECT
 '3','5',5 UNION
SELECT
 '3','2',1 UNION
SELECT
 '3','3',1 UNION
SELECT
 '3','3',3

In the other article, we hard coded the column names (1,2,3), however, this may not always be viable, sometimes the record sets may need to grow when for example sending a data set to a GUI, there is a way around it, lets go through it step by step; 

  • Create a Table Type to represent your data as above.
  • Declare a table and insert your data into it (@Piv)
  • Declare two variables, one to hold the column names to Pivot, and one for the SELECT list, in this example, using a ISNULL and the column names gets rid of any NULL values. We have also used COALESCE to concatenate the names into a string.
  • Declare another variable, with the @Piv table, these are parameters that get passed on later.
  • Declare our SQL, this is where we inject the column names and select list into a modified version of the simple PIVOT.
  • EXECUTE the SQL statement above, and pass in the parameters and @PIV table.
--Complex Pivot - unknown column names
--Get Column Names for below
DECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) 
SELECT @Cols=COALESCE(@Cols+',','')+'['+MyCol+']',--Concatenate the Columns
  @Sel =COALESCE(@Sel +',','')+'ISNULL(['+MyCol+'], 0) AS ['+MyCol+']' --Concatenate the Columns into a select list
FROM @Piv GROUP BY MyCol 

--Columns list display
SELECT @Cols ColumnList,@Sel SelectList

--Complex Pivot SQL
DECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'
DECLARE @SQL NVARCHAR(MAX)=
'SELECT [MyRow],'+@Sel+'
FROM @Piv
PIVOT (SUM(CalcCol)
FOR MyCol
IN ('+@Cols+')
) AS MyTable'

--Execute SQL
EXECUTE sp_executesql @SQL,--SQL String from above
@Params,--Parameter list
@Piv--Temp Table needs to be passed in, can only be read only?

Our dataset has now grown to include two additional columns from the dataset (5,s)

MyRow1235s
110101
207100
301450

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2021, registered in England and Wales 08985867

Site Links

RSS Login ContactCookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partnered With

Partners
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader