Dynamic Pivot Without Aggregation Function in SQL Server

In this article, I will explain How to create a

Dynamic Pivot Without Aggregation Function in SQL Server

Check also Convert Rows To Columns Using Dynamic Pivot In SQL Server.


Scenario

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.

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.

Dynamic Pivot Without Aggregation Function in SQL Server

Check also Convert Rows To Columns Using Dynamic Pivot In SQL Server.


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)

Dynamic Pivot Without Aggregation Function in SQL Server

Convert the data output into a PIVOT form. But, I don’t have anything to aggregate in the table; to show the data as shown below!

Output (Figure2)

Dynamic Pivot Without Aggregation Function in SQL Server

I will use the following query to 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 is a list of dynamic fields that represent the columns name as shown in Figure 2

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

Then I need to build Pivot Statement as the following syntax

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 '

So the final query should look 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 unfortunately, it retrieves only one row!

This occurs because I didn’t use an aggregation function during building the Pivot.

To overcome this issue I modified 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


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)


Applies To
  • SQL Server 2016.
  • SQL Server 2014.
  • SQL Server 2012.
  • SQL Server 2008.
See Also
Advertisements

2 thoughts on “Dynamic Pivot Without Aggregation Function in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s