SharePoint / EPM

Convert Rows To Columns Using Dynamic Pivot In SQL Server

Advertisements

In this article, I will explain

How to convert rows to columns using Dynamic Pivot in SQL Server?

Download The code sample for this demo from TechNet Gallery: Dynamic Database Structure – Convert Rows to Columns Using Dynamic Pivot


Scenario

We have decided to build a dynamic database by creating only three tables as shown below:

The desired data should look like:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that are wanted in the final output.


Steps

Get a list of the “Field Values” (Rows)

Get a list of “Field Values” that should be shown as Rows by doing the following:

BEGIN try
 DROP TABLE ##dataquery
END try

BEGIN catch
END catch

CREATE TABLE ##dataquery
 (
 id INT NOT NULL,
 tablename VARCHAR(50) NOT NULL,
 fieldname VARCHAR(50) NOT NULL,
 fieldvalue VARCHAR(50) NOT NULL
 );
INSERT INTO ##dataquery
SELECT Row_number()
 OVER (
 partition BY (fields.fieldname)
 ORDER BY fieldvalue.fieldvalue) ID,
 tables.tablename,
 fields.fieldname,
 fieldvalue.fieldvalue
FROM tables
 INNER JOIN fields
 ON tables.tid = fields.tid
 INNER JOIN fieldvalue
 ON fields.fid = fieldvalue.fid
WHERE tables.tid = @TableID

Output

Get a list of the “Fields” (Columns)

Retrieve the list of the fields filtered by “TableID” by using the below SQL statement


DECLARE @DynamicColumns AS VARCHAR(max)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
 + Quotename(fieldname)
FROM (SELECT DISTINCT fieldname
 FROM fields
 WHERE fields.tid = @TableID) AS FieldList

COALESCE: Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Quotename: Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.Build the Dynamic Pivot Table Query

Build the Dynamic Pivot Table Query

In this section, we will combine the above two queries to can build our Dynamic Pivot


DECLARE @FinalTableStruct AS NVARCHAR(max)

SET @FinalTableStruct = 'SELECT ' + @DynamicColumns +
' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in (' + @DynamicColumns + ') ) p '

EXECUTE(@FinalTableStruct)

Output


Convert rows to columns using Dynamic Pivot in SQL Server

Finally, the Dynamic Pivot Script should look like


DECLARE @TableID AS INT

SET @TableID=1 -- Set Table ID

-- Get a list of the "Field Value" (Rows)
BEGIN try
DROP TABLE ##dataquery
END try

BEGIN catch
END catch

CREATE TABLE ##dataquery
(
id INT NOT NULL,
tablename VARCHAR(50) NOT NULL,
fieldname VARCHAR(50) NOT NULL,
fieldvalue VARCHAR(50) NOT NULL
);

INSERT INTO ##dataquery
SELECT Row_number()
OVER (
partition BY (fields.fieldname)
ORDER BY fieldvalue.fieldvalue) ID,
tables.tablename,
fields.fieldname,
fieldvalue.fieldvalue
FROM tables
INNER JOIN fields
ON tables.tid = fields.tid
INNER JOIN fieldvalue
ON fields.fid = fieldvalue.fid
WHERE tables.tid = @TableID

--Get a list of the "Fields" (Columns)
DECLARE @DynamicColumns AS VARCHAR(max)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')
+ Quotename(fieldname)
FROM (SELECT DISTINCT fieldname
FROM fields
WHERE fields.tid = @TableID) AS FieldList

--Build the Dynamic Pivot Table Query
DECLARE @FinalTableStruct AS NVARCHAR(max)

SET @FinalTableStruct = 'SELECT ' + @DynamicColumns
+
' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in ('
+ @DynamicColumns + ') ) p '

EXECUTE(@FinalTableStruct)

Output


Applies To

Download

To download the Database Backup and the Dynamic Pivot Script Check TechNet Gallary.

 

Advertisements

Advertisements