Using PIVOT with Static and Dynamic records

We can use the PIVOT relational operators to change a table-valued expression into another table like ROW(s) to COLUMN(s).

UNPIVOT performs the opposite operation to PIVOT by rotating COLUMN(s) to ROW(s).

Creating Table(s) and Sample records:
IF OBJECT_ID('Tb_DataTypeCategory','U') IS NOT NULL
DROP TABLE Tb_DataTypeCategory
GO
CREATE TABLE Tb_DataTypeCategory
(
CategoryId INT IDENTITY(1,1),
Category VARCHAR(50)
)
GO
 
INSERT Tb_DataTypeCategory(Category) VALUES('Approximate numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Binary strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Character strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Date and time')
INSERT Tb_DataTypeCategory(Category) VALUES('Exact numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Other data types')
INSERT Tb_DataTypeCategory(Category) VALUES('Unicode character strings')
GO
 
IF OBJECT_ID('Tb_DataType','U') IS NOT NULL
DROP TABLE Tb_DataType
GO
CREATE TABLE Tb_DataType
(
Id INT IDENTITY(1,1),
DataType VARCHAR(50),
CategoryId INT
)
GO
INSERT Tb_DataType(DataType,CategoryId) VALUES('FLOAT',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('REAL',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('IMAGE',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARBINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARCHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TEXT',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATE',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLDATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME2',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIMEOFFSET',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIGINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DECIMAL',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('INT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('MONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NUMERIC',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLMONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TINYINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CURSOR',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIMESTAMP',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('HIERARCHYID',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SQL_VARIENT',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TABLE',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('UNIQUEIDENTIFIER',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('XML',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NVARCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NTEXT',7)
Go

Actual Result:
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK) ON(dt.CategoryId = dtc.CategoryId) ORDER BY dtc.Category,dt.DataType
Go

 
 
 
 
 
 
 
 
 
 
 

Using PIVOT with Static Record(s)
SELECT
[Approximate numerics]
,[Binary strings]
,[Character strings]
,[Date and time]
,[Exact numerics]
,[Other data types]
,[Unicode character strings]
FROM
(
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK)
ON(dt.CategoryId = dtc.CategoryId)
)AS ActualData
PIVOT
(
COUNT(DataType)
FOR Category IN([Approximate numerics],[Binary strings],[Character strings],[Date and time],[Exact numerics],[Other data types],[Unicode character strings])
)AS PIVOTData
Go

Result:

I am adding one more Category and Data Types in Tb_DataTypeCategory, Tb_DataType respectively.

But, The result will be the same. Because we have hardcoded the Categories in this static query. Then How to overcome this loss of records.

We can move to Dynamic query.

Using PIVOT with Dynamic Record(s):
1. Concatenating the Categories into a Variable: @ChvCategories

DECLARE @ChvCategories VARCHAR(MAX) , @ChvnSql NVARCHAR(MAX)
SELECT @ChvCategories = '', @ChvnSql = N' '
SELECT @ChvCategories = COALESCE(@ChvCategories ,'') + '[' + Category + '],' FROM Tb_DataTypeCategory
SELECT @ChvCategories = LEFT(@ChvCategories,LEN(@ChvCategories)-1)
 
2. Using the Concatenated variable in Dynamic query
 
SELECT @ChvnSql = N'
SELECT ' + @ChvCategories + ' FROM
(
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK)
ON(dt.CategoryId = dtc.CategoryId)
)AS ActualData
PIVOT
(
COUNT(DataType)
FOR Category IN(' + @ChvCategories + ')
)AS PIVOTData'


EXEC SP_EXECUTESQL @ChvnSql
Go

Result:

I am adding one more Category and Data Types in Tb_DataTypeCategory, Tb_DataType respectively.

Now, The PIVOT results with added records. Because we have used Dynamic query with PIVOT.