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)

• 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:

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)

• 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.

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

---

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

• Convert Rows To Columns Using Dynamic Pivot In SQL Server.
• SQL Server: Get the Detailed Information Via SERVERPROPERTY.