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.
Pivot is one the new relational operator introduced in SQL Server 2005. It provides easy mechanisms in SQL Server to transform 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 with sample records as depicted in the below image:
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:
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:
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:
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 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 me any improvement 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
No comments:
Post a Comment