SQL Script: Convert Rows To Columns Using Dynamic Pivot In SQL Server

Article
1/17/2024

Introduction

In this article, we will show How to convert rows to columns using Dynamic Pivot in SQL Server. The demo in this article based on a database from the TechNet Gallery.

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.

Scenario

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

https://gallery.technet.microsoft.com/site/view/file/176270/1/Pivot-ERD.png

The “tables” will be used to hold the table names like Users, Products, etc.

https://gallery.technet.microsoft.com/site/view/file/176271/1/Tables.png

The “fields” will be used to hold the fields name related to each table.

https://gallery.technet.microsoft.com/site/view/file/176272/1/Fields.png

The “field value” will be used to hold the value of each field.

https://gallery.technet.microsoft.com/site/view/file/176274/1/Field%20Values.png

The available data:

https://gallery.technet.microsoft.com/site/view/file/176269/1/Dynamic%20Pivot.gif

The desired data should look like:

https://gallery.technet.microsoft.com/site/view/file/176268/1/Dynamic%20Pivot%20output.gif

Steps

* Download the sample database here.

Get a list of the "Field Values" (Rows)

In this section, we will get a list of "field values" that should be shown as rows by doing the following:

Create a table "dataquery" that will hold the field values data.

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
  );

Query the field values data filtered by "TableID" and insert the output into the created table in the above step.

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

https://gallery.technet.microsoft.com/site/view/file/176275/1/Dynamic%20Pivot%20Q1.png

Get a list of the "Fields" (Columns)

In this section, we will 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

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

https://gallery.technet.microsoft.com/site/view/file/176276/1/Dynamic%20Pivot%20Q2.png

The Final Dynamic Pivot Query

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
**

https://gallery.technet.microsoft.com/site/view/file/176277/1/Dynamic%20Pivot%20Q3.png

Applies To

SQL Server 2016.

SQL Server 2012.

SQL Server 2008.

Download

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

Conclusion

In this article, we have explained How to use Pivot to convert rows as columns in SQL Server.

Share via