SQL Server PIVOT

Dynamic Pivoting in SQL Server 

Introduction

PIVOT is the SQL Server keyword used to transpose the row data into column fashion. We can also say, pivot allows us to create column grouping.

Case Study

We have a table with Attribute (keeps city name) and value (contains sales Amt) as shown below.

We have to design a query which can work if any city city gets added.

Solution

Since we have to design the query such a way it can dynamically adjust the new city to new column, we need to use SQL dynamic query which can be stored in SQL variable and later executed through EXEC or SP_EXECUTESQL. We also have to keep in mind about the limitation of SQL the range of NVARCHAR(MAX) and columns.

-- Create table that accomodates Attributes and Values

CREATE TABLE tblAttVal

(

[Attribute] NVARCHAR(255),

[Value] NVARCHAR(255)

)

--Insert values to the table

INSERT INTO tblAttVal VALUES('Bangalore','4000')

INSERT INTO tblAttVal VALUES('Hyderabad','5000')

INSERT INTO tblAttVal VALUES('Bangalore','3000')

INSERT INTO tblAttVal VALUES('Hyderabad','2500')

--DECLARE SQL variables

DECLARE @ColList NVARCHAR(MAX) = '', @SQLQuery NVARCHAR(MAX) = ''

--Collect the list of colums for pivoting

SELECT @ColList = @ColList + '['+ [Attribute]+'],' FROM

(

SELECT DISTINCT [Attribute]

FROM tblAttVal) A

SET @ColList = LEFT(@ColList,LEN(@ColList) - 1)

SET @SQLQuery = 'SELECT * FROM 

(SELECT [Attribute],CAST([Value] AS INT) AS [Value] FROM tblAttVal ) P

PIVOT (SUM([Value]) FOR [Attribute] IN ('+@ColList+')) A'

EXEC(@SQLQuery)