---
title: "Pivot with Dynamic columns in SQL Server"  
description: "Pivot is one the new relational operator introduced in SQL Server 2005. It provides easy mechanisms in SQL Server to transform rows into columns."  
author: "Chris Anderson"  
published: 2014-10-08  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1511/pivot-with-dynamic-columns-in-sql-server  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 6 minutes  

---

# Pivot with Dynamic columns in SQL Server

In this article I will present how we can write a Dynamic PIVOT query with an example, where we don’t need to mention the PIVOT columns each [unique values](https://answers.mindstick.com/qa/93939/how-to-define-hashset-t-collection-in-c-sharp-which-collection-is-used-to-store-unique-values).

Pivot is one the new [relational](https://www.mindstick.com/blog/10885/equality-relational-and-conditional-operators) [operator](https://www.mindstick.com/blog/144/union-intersection-and-except-operator-in-sql-server) introduced in SQL Server 2005. It provides easy mechanisms in [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) to [transform](https://www.mindstick.com/interview/511/what-are-the-steps-to-transform-xml-into-html-using-xsl) rows into columns.

Let’s move towards our example, sql script for table and sample records are given at the end of this article.

First Create a Table [Product](https://yourviews.mindstick.com/view/83909/why-mindstick-platform-is-so-important-for-our-product-promotions) with sample records as depicted in the below image:

![Pivot with Dynamic columns in SQL Server](https://www.mindstick.com/mindstickarticle/8c51b7e5-92d6-42cc-9d09-57bfd4ad8e00/images/f9761b7d-56b6-4a2d-85c3-99af67653d11.png)

```
CREATE TABLE [dbo].[Product](
      [ProductId] [bigint] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL
)
```

##### Now create a table Customer by using following script:

![Pivot with Dynamic columns in SQL Server](https://www.mindstick.com/mindstickarticle/8c51b7e5-92d6-42cc-9d09-57bfd4ad8e00/images/8fc2e8ea-5609-4e80-a551-656b01c05746.png)

```
CREATE TABLE [dbo].[Customer](
      [CustomerId] [bigint] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL,
      [City] [varchar](50) NOT NULL
)
```

##### Last and final table Order script is given below:

![Pivot with Dynamic columns in SQL Server](https://www.mindstick.com/mindstickarticle/8c51b7e5-92d6-42cc-9d09-57bfd4ad8e00/images/fe7bc2a4-6dee-4020-89fc-d862827890ec.png)

```
CREATE TABLE [dbo].[Order](
      [OrderId] [bigint] IDENTITY(1,1) NOT NULL,
      [OrderNum] [varchar](50) NOT NULL,
      [ProductId] [bigint] NOT NULL,
      [CustomerId] [bigint] NOT NULL,
      [OrderDate] [datetime] NOT NULL
)
```

Let us first understand the STATIC PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.

```
SELECT * FROM
(
  Select p.[Name] as [Product], c.[City] as [City]
            From [Order] o inner join [Customer] c on o.CustomerId = c.CustomerId
                                 inner join [Product] p on o.ProductId = p.ProductId
) AS t
PIVOT
(
  Count(City)
  FOR City IN([Ahmedabad],[Allahabad],[Delhi],[Kanpur],[Lucknow],[Ranchi],[Varanasi])
) AS p ;
```

By using the above query we want to show that how many orders has been received for the product from the specific City. The out should be something like below:

![Pivot with Dynamic columns in SQL Server](https://www.mindstick.com/mindstickarticle/8c51b7e5-92d6-42cc-9d09-57bfd4ad8e00/images/bcb22f33-c8e6-4edb-b7b7-be298d1b588e.png)

##### Dynamic PIVOT Query

To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT [operators](https://www.mindstick.com/articles/13159/operating-press-brakes-an-ultimate-guide-for-brake-operators) PIVOT columns IN clause. The query is demonstrated below:

```
DECLARE @cols AS NVARCHAR(MAX) = '';
DECLARE @query AS NVARCHAR(MAX);

select @cols += STUFF((SELECT distinct ',' +
                        QUOTENAME(City)
                      FROM Customer
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
  Select p.[Name] as [Product], c.[City] as [City]
            From [Order] o inner join [Customer] c on o.CustomerId = c.CustomerId
                                 inner join [Product] p on o.ProductId = p.ProductId
) AS t
PIVOT
(
  Count(City)
  FOR City IN( ' + @cols + ' )' +
' ) AS p ; ';

execute(@query);
```

Thanks for reading this article. [Please suggest](https://answers.mindstick.com/qa/43506/please-suggest-the-bathing-date-at-kumbh) me any [improvement](https://answers.mindstick.com/qa/32074/which-state-introduces-land-improvement-schemes-act-bill-to-empower-farmers-in-state) for this article if you find and do not forgot to enter your valuable comments.

##### SQL Script for this article example:

```
CREATE TABLE [dbo].[Product](
      [ProductId] [bigint] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL
)
SET IDENTITY_INSERT [dbo].[Product] ON

INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (1, N'Coke')
INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (2, N'Pepsi')
INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (3, N'Fanta')
INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (4, N'Mt. Dew')
INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (5, N'Pepsi Next')
INSERT [dbo].[Product] ([ProductId], [Name]) VALUES (6, N'Diet Coke')

SET IDENTITY_INSERT [dbo].[Product] OFF

CREATE TABLE [dbo].[Customer](
      [CustomerId] [bigint] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL,
      [City] [varchar](50) NOT NULL
)
SET IDENTITY_INSERT [dbo].[Customer] ON

INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (1, N'Rohit Kesharwani', N'Allahabad')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (2, N'Rahul Singh', N'Kanpur')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (3, N'Rajeev Bhatia', N'Varanasi')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (4, N'Neha Pandey', N'Lucknow')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (5, N'Anushka Sharma', N'Delhi')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (6, N'MS Dhoni', N'Ranchi')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (7, N'Ravindra Jadeja', N'Ahmedabad')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (8, N'Pawan Shukla', N'Allahabad')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (9, N'Kasif Hussain', N'Delhi')
INSERT [dbo].[Customer] ([CustomerId], [Name], [City]) VALUES (10, N'Anil Singh', N'Allahabad')

SET IDENTITY_INSERT [dbo].[Customer] OFF


CREATE TABLE [dbo].[Order](
      [OrderId] [bigint] IDENTITY(1,1) NOT NULL,
      [OrderNum] [varchar](50) NOT NULL,
      [ProductId] [bigint] NOT NULL,
      [CustomerId] [bigint] NOT NULL,
      [OrderDate] [datetime] NOT NULL
)
SET IDENTITY_INSERT [dbo].[Order] ON

INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (1, N'001', 1, 1, CAST(0x0000A139009CA7E8 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (2, N'002', 2, 1, CAST(0x0000A15800CDFE60 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (4, N'003', 2, 2, CAST(0x0000A17500377B20 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (5, N'004', 3, 3, CAST(0x0000A1940044CD48 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (6, N'005', 4, 4, CAST(0x0000A1B3006B2998 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (7, N'006', 4, 5, CAST(0x0000A1D10130DEE0 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (8, N'007', 4, 6, CAST(0x0000A1EF014F1540 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (9, N'008', 5, 6, CAST(0x0000A20E0172C9E0 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (10, N'009', 6, 7, CAST(0x0000A22D018B3BB0 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (11, N'010', 6, 8, CAST(0x0000A25F002932E0 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (12, N'011', 1, 9, CAST(0x0000A2600041EB00 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (13, N'012', 2, 10, CAST(0x0000A26E0083D600 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (14, N'013', 3, 1, CAST(0x0000A28E00602160 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (17, N'014', 6, 2, CAST(0x0000A1D100107AC0 AS DateTime))
INSERT [dbo].[Order] ([OrderId], [OrderNum], [ProductId], [CustomerId], [OrderDate]) VALUES (18, N'015', 5, 4, CAST(0x0000A1F1002BF200 AS DateTime))

SET IDENTITY_INSERT [dbo].[Order] OFF
```

---

Original Source: https://www.mindstick.com/articles/1511/pivot-with-dynamic-columns-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.