Count All Tasks in Project Server

In this article, I will explain How to

Calculate the Tasks count in Project Schedule in Project Server

Count All Tasks in Project Server

As shown above, the count of total tasks below the main Summary task is 6 Continue reading “Count All Tasks in Project Server”

Advertisements

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.

 

This o15.officeredir.microsoft.com page can’t be found when Edit List In SharePoint Designer

Scenario

I have tried to click on edit list in SharePoint Designer option from the list ribbon,

Edit List In SharePoint Designer from browser

but unfortunately, I got the below error.

This o15.officeredir.microsoft.com page can’t be found

No webpage was found for the web address:http://o15.officeredir.microsoft.com/r/rlidSPD2013Download?clid=undefined
HTTP ERROR 404

I have SharePoint Server 2013, and the SharePoint Designer 2013 is already installed.

Also, in Central Administration, the SharePoint Designer has been allowed to be used in this Web Application as shown below.

This o15.officeredir.microsoft.com page can’t be found
Cause Continue reading “This o15.officeredir.microsoft.com page can’t be found when Edit List In SharePoint Designer”

Missing Security Settings in Project Online

In this article, We will discuss and solve the issue of

Missing Security Setting in PWA Settings in Project Online.


Scenario

In Project Online > PWA Settings, you can’t find the Security Settings as shown below.
Missing Security Settings in Project Online


Cause

Continue reading “Missing Security Settings in Project Online”

Show / Hide fields based on a dropdown selection using SPUtility.js

In this article, I’ll explain How to show and hide fields based on a Choice field selection in ‘New Form’, ‘Edit Form’ and ‘Display Form’ in SharePoint 2013 / 2016 via SPUtility.js?

Prerequisites:

  • Download SPUtility.js.
  • Upload the downloaded file (SPUtility.js) to an appropriate place in SharePoint Site like ‘Style Library’.
  • Create a Custom list.
    • Add a choice field displayed as ‘Drop-Down Menu‘.
    • Fill your choices as you need.
    • Add the fields that you need to show or hide based on the selected value of choice field.

Steps:

Continue reading “Show / Hide fields based on a dropdown selection using SPUtility.js”

Missing Data Connection Library In SharePoint

In this hint,  I will elaborate How to show the missing Data Connection Library In SharePoint?

Scenario:
If you are working on InfoPath or Excel Services in SharePoint, you will need to use a Data Connection Library to save your external connection files like a “udcx” file.
But sometimes, when you tried to add a new Data Connection Library you may miss it in Add an App Page.

missing-data-connection-libraryCause: Continue reading “Missing Data Connection Library In SharePoint”

Hide List View Header Menu via CSS in SharePoint

In this hint, I’ll show How to hide SharePoint ListView column header menu via CSS for print purpose.open-menu-option-in-listviewSteps : Continue reading “Hide List View Header Menu via CSS in SharePoint”