Dynamic pivot without aggregate function in SQL Server

In this article, we will learn How to create a dynamic pivot without aggregate function in SQL Server?

You might also like to read Convert Rows To Columns Using Dynamic Pivot In SQL Server


Dynamic pivot without aggregate function in SQL Server

I have the below three tables (One-Many relations)

Dynamic Pivot Without Aggregation Function in SQL Server
  • The 1st table (Table) holds tables name.
  • The 2nd table (Fields) is related to (Table) to hold the fields name for each table.
  • The 3rd table (Field Value) is related to (Fields) to hold fields value for each field.

So, I would like to show all related data (Fields and its value) for each table name (Entity) in the 1st table to be structured as a real independent table as shown below:

Dynamic Pivot Without Aggregation Function in SQL Server

Convert Rows To Columns Using Dynamic Pivot

Steps

  • Get each field values for each field for a specific table using the following query (Query1)
SELECT
Fields.FieldName COL0,
FieldsValue.Value COL1
FROM Fields
LEFT OUTER JOIN FieldsValue
ON Fields.Field_ID = FieldsValue.Field_ID
AND Fields.Table_ID = FieldsValue.Table_ID
INNER JOIN [Table]
ON Fields.Table_ID = [Table].Table_ID
WHERE (Fields.Entity_ID = @Table_ID)

Output (Figure1)

SQL dynamic pivot without aggregate
  • Retrieve only the unique values from COL0 in Figure 1 (Query2)
DECLARE @DynamicColumns AS nvarchar(max)
SET @DynamicColumns = STUFF((SELECT DISTINCT
'],[' + COL0
FROM (SELECT
Fields.FieldName COL0
FROM Fields
WHERE (Fields.Table_ID = @Table_ID)) y
FOR xml PATH (''), TYPE)
.value('.', 'VARCHAR(Max)'), 1, 2, '') + ']'

This Output of the above query would be like a list of dynamic fields that represent the columns name as shown below:

[CanAdd],[CanDelete],[CanSearch],[CanUpdate],[RoleArName],[RoleEnName],
[RoleId],[ShowAdminMenu]

Here, I will merge the query1 and query2 to to build Pivot Statement as the following:

DECLARE @DynamicColumns AS NVARCHAR(MAX)
DECLARE @GeneratedQuery AS NVARCHAR(MAX)
Set @DynamicColumns="Query2 that wrote above"
Set @GeneratedQuery = 'SELECT ' + @DynamicColumns + ' from
(
"Query1 that wrote above"
) x
pivot
(
max( COL1) -- column that has value
for COL0 in (' + @DynamicColumns + ')
) p '

The merge result should looks like the following:

DECLARE @DynamicColumns AS NVARCHAR(MAX)
DECLARE @GeneratedQuery AS NVARCHAR(MAX)
DECLARE @Table_ID as nvarchar(50)
set @Table_ID = '7'

SET @DynamicColumns=
Stuff((Select Distinct '],['+COL0 From
(select Fields.FieldName COL0 FROM Fields WHERE (Fields.Table_ID = @Table_ID) )
y for Xml Path(''),Type).value('.','VARCHAR(Max)'), 1, 2,'')+']'

SET @GeneratedQuery = 'SELECT ' + @DynamicColumns + ' from
(
SELECT Fields.FieldName COL0, FieldsValue.Value COL1 FROM Fields
LEFT OUTER JOIN FieldsValue
ON Fields.Field_ID = FieldsValue.Field_ID AND
Fields.Table_ID = FieldsValue.Table_ID INNER JOIN Table
ON Fields.Table_ID = Table.Table_ID WHERE (Fields.Entity_ID =@Table_ID )
) x
pivot
(
max( COL1)
for COL0 in (' + @DynamicColumns + ')
) p '
execute(@GeneratedQuery)

Although The previous query converts the structure successfully. but it retrieves only one row! This occurs because I didn’t use an aggregation function during building the Pivot.

To overcome this issue I will use ROW_NUMBER() at the Query1 to be as the following :

SELECT
ROW_NUMBER() OVER (PARTITION BY (Fields.FieldName)
ORDER BY FieldsValue.FieldsValueID) iid,
Fields.FieldName COL0,
FieldsValue.Value COL1
FROM Fields
LEFT OUTER JOIN FieldsValue
ON Fields.Field_ID = FieldsValue.Field_ID
AND Fields.Table_ID = FieldsValue.Table_ID
INNER JOIN [Table]
ON Fields.Table_ID = [Table].Table_ID
WHERE (Fields.Entity_ID = @Table_ID)

Where the column iid will partition the values with index and make each row unique as shown below.

Dynamic Pivot Without Aggregation Function in SQL Server

So the Final PIVOT Query should look like the following query

DECLARE @DynamicColumns AS nvarchar(max)
DECLARE @GeneratedQuery AS nvarchar(max)
DECLARE @Table_ID AS nvarchar(50)
SET @Table_ID = '7'
SET @DynamicColumns =
STUFF((SELECT DISTINCT
'],[' + COL0
FROM (SELECT
Fields.FieldName COL0
FROM Fields
WHERE (Fields.Table_ID = @Table_ID)) y
FOR xml PATH (''), TYPE)
.value('.', 'VARCHAR(Max)'), 1, 2, '') + ']'

SET @GeneratedQuery = 'SELECT ' + @DynamicColumns + ' from
(
SELECT row_number() OVER (PARTITION BY (Fields.FieldName)
order BY FieldsValue.FieldsValueID) iid,
Fields.FieldName COL0, FieldsValue.Value COL1 FROM Fields LEFT OUTER JOIN
FieldsValue
ON Fields.Field_ID = FieldsValue.Field_ID AND
Fields.Table_ID = FieldsValue.Table_ID INNER JOIN [Table]
ON Fields.Table_ID = [Table].Table_ID WHERE (Fields.Entity_ID =@Table_ID )
) x
pivot
(
max( COL1)
for COL0 in (' + @DynamicColumns + ')
) p '
EXECUTE (@GeneratedQuery)

Output

Dynamic pivot without aggregate function in SQL Server

Conclusion

In conclusion, we have learned how to perform dynamic pivot without aggregate function in SQL Server to convert rows as a columns header for a table in SQL Server.

Applies To

This sample has been tested on the following SQL Server versions:

  • SQL Server 2016.
  • SQL Server 2014.
  • SQL Server 2012.
You might also like to read

2 thoughts on “Dynamic pivot without aggregate function in SQL Server”

Leave a Reply