Converting rows to columns in SQL

Converting columns into rows will be a usual scenario when we deal with databases. Here we can see a basic example of displaying the data stored in row as column.

Following is the table with Employee Id mapped to Transfer Id. This table stores only the employee id and transfer id.

Transfer Table

Transfer ID references employee transfer details of a detailed table given below. The detailed table (Approver Table) may or may not have approver employee id (APPRVR) for every transfer id in the Approver Table

Approver Table

The Approver Employee Ids (APPRVR) has to be displayed column wise. If there is no Approver Employee Id, it has to be marked as NULL.

EMP ID	TRNSFR ID	1STAPPRVR	2NDAPPRVR	3RDAPPRVR

Check the below query and see how it works.
--DROP #TEMP

CREATE  TABLE #TEMP(ID INT, TRNSFR_ID INT, [1STAPPRVR] INT, [2NDAPPRVR] INT, [3RDAPPRVR] INT)

INSERT INTO #TEMP

SELECT ID, TRNSFR_ID,

CASE WHEN RNO=1 THEN APPRVR END AS '1STAPPRVR' ,

CASE WHEN RNO=2 THEN APPRVR END AS '2NDAPPRVR' ,

CASE WHEN RNO=3 THEN APPRVR END AS '3RDAPPRVR' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) A

) B

--SELECT * FROM #TEMP

SELECT TRNSFR_ID,MAX([1STAPPRVR]) AS '1STAPPRVR', MAX([2NDAPPRVR]) AS '2NDAPPRVR', MAX([3RDAPPRVR]) AS '3RDAPPRVR' FROM #TEMP  AS C

GROUP BY C.TRNSFR_ID

The above query results as the below screen

This can be Joined with Transfer Table and taken employee wise approver numbers.

There are lot other ways to do the same. The following is just an alternative.

-- START

SELECT X.ID, X.TRNSFR_ID, X.APP_1, Y.APP_2 FROM (

SELECT ID, TRNSFR_ID, APPRVR as 'APP_1' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) A

WHERE A.RNO = 1) B

 )AS X LEFT JOIN (

SELECT ID, TRNSFR_ID, APPRVR as 'APP_2' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) C

WHERE C.RNO = 2) D

) AS Y  ON X.TRNSFR_ID = Y.TRNSFR_ID

--END

I have added only 1st and 2nd approver here. This way the same query can be extended to display the 3rd approver also.

Rather than doing inner join with derived tables, it can be inserted in a temp tables with the same way to get it not complicated and do join.

Generating Excel Report with Microsoft.Office.Interop.Excel.dll and C#

I was developing a page which generated Excel report from code behind and the excel report to be password protected and to be maied to the user group to which the user who clicked on report generation belongs to. I had added a reference to Microsoft Excel 12.0 Object Library under the COM tab when I did the "Add Reference".

The server machine (production server) where I deployed the application/page did not have MS Office or Excel installed. The report generation was not happening. I was getting the following error:

Retrieving the COM class factory for component with CLSID {00020820-0000-0000-C000-000000000046} failed due to the follo
wing error: 80040154.

It took little time for me to identify that it is just because the excel is not installed on the server. I took the Interop.Excel.dll dll from the GAC of my development machine to the BIN folder of the prodiction server. But this also didnt help. I was stuck with the same error. Realised that there is no other go unless I install Excel or Office on the production server.

You need to have Microsoft Excel installed in order to have access to the Introp libraries that you need to add as references to your project in order to make it work.

To use the features of a Microsoft Office application, we need to use the primary interop assembly (PIA) for the application. The primary interop assembly enables managed code to interact with a Microsoft Office application's COM-based object model.
To apply PIA that can obtain the reference to interop with excel in your program, you have to install Excel.

Here is a nice option of generating excel file without having MS office/MS excel installed on the server, by Carlos Aguilar Mares
http://www.carlosag.net/Tools/ExcelXmlWriter/
BLOG:
http://blogs.msdn.com/b/carlosag/archive/2005/08/27/whyiwroteexcelxmlwriter.aspx

Securing an ASP.Net WebService

Implementing authentication for Webservice is quite simple. This is one of the several methods in implementing security for WebServices.

The WebService you created should have a custom SOAPHeader. We pass the credentials with  SOAPHeader to the server during the WebService calls and get it validated on server. The response of the WebMethod will be sent back only if the user is authenticated.

We will have to include the custom SOAPHeader that we want to include in the SOAP message.
For this, we will have to write a separate class(UserCredentials) which inherits from [System.Web.Services.Protocols.SoapHeader] with two properties. (UserName and Password)

My WebService to be hosted on a remote Server

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(Name = "KochiService", ConformsTo = WsiProfiles.BasicProfile1_1)]

public class KochiService : System.Web.Services.WebService {

public UserCredentials Credentials;
public KochiService()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
[SoapDocumentMethod(Binding = "KochiService")]
[SoapHeader("Credentials", Required= true)]
public string GetEmployeeSalary() {
if (AuthenticateUser())
{
   return "This user's Salary is 10,000";
}
else
{
    return "You are not authorised to consume this service";
}
}
private bool AuthenticateUser()
{
if ((Credentials.UserName == "Sabin") && (Credentials.Password == "1234"))
{
   return true;
}
else
{
   return false;
} 
} 
}

For Security, Custom SOAP Header Class; User Credentials

using System;
using System.Collections.Generic;
using System.Web;

public class UserCredentials : System.Web.Services.Protocols.SoapHeader
{
    public string UserName;
    public string Password;
}


HOSTING THE WEB SERVICE.
Build the solution and deploy the WebService files on remote server.
.ASMX on the root of the Website or Virtual Directory and the .CS file in the App_Code of the ASP.Net Website.

Also make sure that the UserCredential class got deployed on the server under App_Code.
After hosting you will be able to view the details of the webservice by accessing the .ASMX page as www.MyWebsite.com/KochiService.asmx




Now, your WebService is available online for all the users. But only those pass the exact Credentials will ONLY be able to get the response from the remote machine(WebService).



CONSUMING THE WEBSERVICE

Those who have the correct Credentials for the WebService can access the service from anywhere in  a .Net Website over the internet. For accessing the WebService, we need to add the service through the ServiceReferance by right clicking on the WebSite Project in Visual Studio. While adding, it creates a proxy to call the service from the solution. 


Calling WebService from .aspx Page remotely

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class WebServiceCalls : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
MyServiceReference.KochiServiceClient oService = new MyServiceReference.KochiServiceClient();
MyServiceReference.UserCredentials oCredentials = new MyServiceReference.UserCredentials();

oCredentials.UserName = "Sabin";
oCredentials.Password = "1234";

Response.Write(oService.GetEmployeeSalary(oCredentials));

}
}

The service will return the response based on the authentication as below



Sending XML to Stored Procedure

The need of sending a series of strings to Stored Procedure will be there in all most all the projects. XML variables in SQL Server make it easy to deal with XML strings into relational databases. The new methods we should use are value() and nodes() which allow us to select values from XML documents.



DECLARE @Employees xml
SET @Employees = '1908210174'

SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Employees.nodes('/Employees/id') as ParamValues(ID)



The above SQL statements returns three rows as below:
1908
2101
74

Now, let us see how this can be used to fetch the Employee information for a list of Employee Ids. Take a look at the Stored Procedure.

CREATE PROCEDURE GetEmployeesDetailsForThisList(@EmployeeIds xml) AS
DECLARE @Employees TABLE (ID int)

INSERT INTO @Employees (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @EmployeeIds.nodes('/Employees/id') as ParamValues(ID)

SELECT * FROM
    EmployeeTable
INNER JOIN 
    @EmployeeIds e
ON    EmployeeTable.ID = e.ID

This Stored Procedure can be called as

EXEC GetEmployeesDetailsForThisList '1908210174'





XML public static string BuildEmployeesXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();

    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("{0}", values[i]);
    }
    xmlString.AppendFormat("", xmlRootName);
    return xmlString.ToString();
}

This above ASP.NET method will return XML String, and this can be sent as the input parameter to Stored Procedure