Convert Rows To Columns Using Dynamic Pivot In SQL Server

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:

convert rows to columns using Dynamic Pivot in SQL Server

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

Tables - convert rows to columns using Dynamic Pivot in SQL Server

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

convert rows to columns using Dynamic Pivot in SQL Server

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

Field Values - convert rows to columns using Dynamic Pivot in SQL Server

  • The relationship between the three tables is one-many and the ERD looks like the below:

convert rows to columns using Dynamic Pivot in SQL Server

The desired data should look like:

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.


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:

  • Create a table “data-query” that will hold the field data values.
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

convert rows to columns using Dynamic Pivot in SQL Server

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


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
How to use Dynamic Pivot in SQL


Applies To
  • SQL Server 2017.
  • SQL Server 2016.
  • SQL Server 2012.
  • SQL Server 2008.

Download

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

 

Advertisements

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

In this article, I will explain How to trace and solve the below error

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment.
This could be because the pre-login handshake failed or the server was unable to respond back in time.

connection-timeout-expired.png


Scenario

Continue reading “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment”

How to Check SQL Server Instance Connectivity from the application server to database server for a specific user

In this article, I will explain

Check the connectivity between Application Server and SQL Server Instance in Database Server.


Scenario

If you are working in an implementation team, you will walk through the following:

  • Check the connectivity between the application server and database server.
  • Make sure that the SQL Server port (default is 1433) is opened and accessible through the application server.
  • Make sure that the web site application pool account can access a specific database.
  • Make sure that the SQL Server account can access a specific database.

Check the connectivity between the application server and database server.

Continue reading “How to Check SQL Server Instance Connectivity from the application server to database server for a specific user”

Get The build number of the latest Cumulative Update / Service Pack that was installed in SQL Server

In this article, I’ll show How to

Get the build number of the latest Cumulative Update / Service Pack that has been installed in SQL Server.

Scenario:

I decided to configure PowerPivot for SharePoint 2013 Enterprise Edition with Service Pack 1 that requires installing SQL Server 2012 Service Pack 1 as a prerequisite.

So that I need to check if the SQL Server Service Pack 1 has been installed on SQL Server or not!

So in this article, by getting the build number of the latest Cumulative Update / Service Pack that has been installed in SQL Server by following the mentioned below:

Steps: Continue reading “Get The build number of the latest Cumulative Update / Service Pack that was installed in SQL Server”

Shrink a transaction log file Maintenance Plan in SQL Server

The Major factor that faces me when I was trying to create a Shrink a transaction log file Maintenance Plan is the shrink option in Maintenance Tasks that include shrink operation for both data file and transaction log file, and there is not an option for only log file!

maintinance plan wizard 29

In this article, I am trying to perform a shrink operation for only the transaction log file via Maintenance Plan to run on a regular basis or on demand based on your requirement.

Before you start, you should be aware of the following

  • To create or manage maintenance plans, you must be a member
    of the sysadmin fixed server role.
  • The SQL Server performance will be affected during executing The Shrink operation. Thence, I advise performing the shrink operation out working hours.
  • The Shrink operation causes index fragmentation and can slow the performance of queries that search a range of the index. So you should consider rebuilding the indexes to eliminate the fragmentation before the shrink operation has been completed .

Therefore, It’s not recommended to perform the shrink operation periodically! Only in some circumstances that you need to reduce the physical size. (Check the possible responses to a full transaction log and suggested how to avoid it in the future).

Steps: Continue reading “Shrink a transaction log file Maintenance Plan in SQL Server”

Create a LOG_BACKUP Maintenance Plan in SQL Server

In this article, I will explain

  1. How to create LOG_BACKUP Maintenance Plan in SQL Server?
  2. How to restore LOG_BACKUP that have already taken via Maintenance Plan?

maintinance plan wizard 10


Keep in mind

Before you getting started, you should be aware of Continue reading “Create a LOG_BACKUP Maintenance Plan in SQL Server”

BACKUP LOG cannot be performed because there is no current database backup in SQL Server

I got the following error:

BACKUP LOG cannot be performed because there is no current database backup in SQL Server

BACKUP LOG cannot be performed because there is no current database backup SSMS1

When I tried to take a log Backup via Management Studio by doing the mentioned steps below: Continue reading “BACKUP LOG cannot be performed because there is no current database backup in SQL Server”