Dynamic Pivot Without Aggregation Function in SQL Server

I have the following ERD
2

  • The first table will hold tables name.
  • The second table will relate to Table to hold the fields name for each table.
  • The third table will relate to field table to hold fields value for each field.

Where The relationship is one to many.

I need to structure all related data for each table name to be structured as a single table and the output look like this.

1

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)

0

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 ! (Figure2)

1

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 represents 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 be 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 retrieve only one row !

This occurres 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.

4

So the Final PIVOT Query should be 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)

Enjoy 🙂

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s