Dynamic SQL Pivot

DYNAMIC SQL PIVOT

One problem with the PIVOT query is that you have to specify all values inside the IN selection if you want to see them as columns.

A quick way to circumvent this problem is to create a dynamic IN selection making your PIVOT dynamic.

DECLARE @query VARCHAR(4000)

DECLARE @languages VARCHAR(2000)

SELECT @languages =

        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]

        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'

SET @query=

'SELECT * FROM

  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks

   FROM BookList) sourceData

PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)

PIVOT query in MS SQL

USING SQL PIVOT

This is perhaps one of the best things that I’ve come across in MS SQL while working on OGC. When you need to quickly convert something from rows to column or column to rows, this is the numero uno solution. Without ado, let me quickly introduce you to the syntax and most common use of a SQL PIVOT keyword.

SYNTAX

The syntax for PIVOT in most simplest expression format as

SELECT *

FROM [SOURCEDATA] PIVOT( SUM([ColumnToBePivotedOn]) FOR [ColumnWhoseRowsWillBeMadeIntoColumns] IN (NewColumn1, NewColumn2, NewColumn3, NewColumn4,…)) AS PivotTableName

 NOTES

1. We can use any aggregate function in lieu of SUM() like MAX(), MIN() etc.
2. [SourceData] can be replaced with a complex source like a JOIN between two table or an inner select query
3. If we need to add more columns to the PIVOT result it can be done provided these are not to be pivoted and will be used for aggregation of pivot data i.e. drilling down further.

EXAMPLE

Imagine a table Books in a Bookstore’s database. For simplicity we assume that the table is quite denormalised and has following columns

• BookId (Primary Key Column)
• Name
• Language
• NumberOfPages
• EditionNumber
• YearOfPrint
• YearBoughtIntoStore
• ISBN
• AuthorName
• Price

NumberOfUnitsSold

CREATE TABLE [dbo].[BookList](

      [BookId] [int] NOT NULL,

      [Name] [nvarchar](100) NULL,

      [Language] [nvarchar](100) NULL,

      [NumberOfPages] [int] NULL,

      [EditionNumber] [nvarchar](10) NULL,

      [YearOfPrint] [int] NULL,

      [YearBoughtIntoStore] [int] NULL,

[NumberOfBooks] [int] NULL,

[ISBN] [nvarchar](30) NULL,

      [AuthorName] [nvarchar](200) NULL,

      [Price] [money] NULL,

      [NumberOfUnitsSold] [int] NULL,

 CONSTRAINT [PK_BookList] PRIMARY KEY CLUSTERED

(

      [BookId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now if we need to query on the database and figure out number of books in English, Russian, German, Hindi, Latin languages bought into the bookstore every year and present our output in a small report format, we can use PIVOT query like this

SELECT * FROM

  (SELECT YearBoughtIntoStore AS [Year Bought],[Language], NumberOfBooks

   FROM BookList) sourceData PIVOT(SUM(NumberOfBooks)

                                   FOR [Language] IN (English, Russian, German, Hindi, Latin)) pivotrReport

This will give a report like

Year Bought	English	Russian	German	Hindi	Latin
2009	24	NULL	50	23	NULL
2010	12	13	NULL	NULL	12
2011	30	NULL	NULL	44	NULL
2012	NULL	NULL	28	NULL	NULL
2013	45	NULL	10	NULL	NULL

We’ll see some more details on this in later blogs. Hope this was helpful.