Post
Ask Question

Convert Rows To Columns Using Dynamic Pivot In SQL Server

In this article, we will show how to convert rows to columns using Dynamic Pivot in SQL Server. 

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.


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



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



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

The available data

The desired data should look like



Steps

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.

  1. BEGIN try  
  2. DROP TABLE ##dataquery  
  3. END try  
  4. BEGIN catch  
  5. END catch  
  6. CREATE TABLE ##dataquery  
  7. (  
  8. id INT NOT NULL,  
  9. tablename VARCHAR(50) NOT NULL,  
  10. fieldname VARCHAR(50) NOT NULL,  
  11. fieldvalue VARCHAR(50) NOT NULL  
  12. );  
Query the field values data filtered by "TableID" and insert the output into the created table in the above step.
  1. INSERT INTO ##dataquery  
  2. SELECT Row_number()  
  3. OVER (  
  4. partition BY (fields.fieldname)  
  5. ORDER BY fieldvalue.fieldvalue) ID,  
  6. tables.tablename,  
  7. fields.fieldname,  
  8. fieldvalue.fieldvalue  
  9. FROM tables  
  10. INNER JOIN fields  
  11. ON tables.tid = fields.tid  
  12. INNER JOIN fieldvalue  
  13. ON fields.fid = fieldvalue.fid  
  14. WHERE tables.tid = @TableID  

Output



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

  1. DECLARE @DynamicColumns AS VARCHAR(max)  
  2. SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')  
  3. + Quotename(fieldname)  
  4. FROM (SELECT DISTINCT fieldname  
  5. FROM fields  
  6. 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 build our Dynamic Pivot

  1. DECLARE @FinalTableStruct AS NVARCHAR(max)  
  2. SET @FinalTableStruct = 'SELECT ' + @DynamicColumns +  
  3. ' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in (' + @DynamicColumns + ') ) p '  
  4. EXECUTE(@FinalTableStruct)  

Output



The Final Dynamic Pivot Query

Finally, the Dynamic Pivot Script should look like the following.

  1. DECLARE @TableID AS INT  
  2. SET @TableID=1 -- Set Table ID  
  3. -- Get a list of the "Field Value" (Rows)  
  4. BEGIN try  
  5. DROP TABLE ##dataquery  
  6. END try  
  7. BEGIN catch  
  8. END catch  
  9. CREATE TABLE ##dataquery  
  10. (  
  11. id INT NOT NULL,  
  12. tablename VARCHAR(50) NOT NULL,  
  13. fieldname VARCHAR(50) NOT NULL,  
  14. fieldvalue VARCHAR(50) NOT NULL  
  15. );  
  16. INSERT INTO ##dataquery  
  17. SELECT Row_number()  
  18. OVER (  
  19. partition BY (fields.fieldname)  
  20. ORDER BY fieldvalue.fieldvalue) ID,  
  21. tables.tablename,  
  22. fields.fieldname,  
  23. fieldvalue.fieldvalue  
  24. FROM tables  
  25. INNER JOIN fields  
  26. ON tables.tid = fields.tid  
  27. INNER JOIN fieldvalue  
  28. ON fields.fid = fieldvalue.fid  
  29. WHERE tables.tid = @TableID  
  30. --Get a list of the "Fields" (Columns)  
  31. DECLARE @DynamicColumns AS VARCHAR(max)  
  32. SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ', '')  
  33. + Quotename(fieldname)  
  34. FROM (SELECT DISTINCT fieldname  
  35. FROM fields  
  36. WHERE fields.tid = @TableID) AS FieldList  
  37. --Build the Dynamic Pivot Table Query  
  38. DECLARE @FinalTableStruct AS NVARCHAR(max)  
  39. SET @FinalTableStruct = 'SELECT ' + @DynamicColumns  
  40. +  
  41. ' from ##DataQuery x pivot ( max( FieldValue ) for FieldName in ('  
  42. + @DynamicColumns + ') ) p '  
  43. EXECUTE(@FinalTableStruct)  

Output



Applies To

  • SQL Server 2016.
  • SQL Server 2012.
  • SQL Server 2008.
Download

To download the Database Backup and the Dynamic Pivot Script, check the attached file or from Gallary.

Conclusion

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

Reference


Similar Articles