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.

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

SET NOCOUNT ON, the performance booster

We normally use SET NOCOUNT ON in STORED PROCEDURES and TRIGGERS
What does actually SET NOCOUNT ON do?

When we normally execute a T-SQL statements (INSERT, UPDATE, SELECT, DELETE) in a query window, we see messages that says something like "(20 row(s) affected)" as the response to our query?

Database's internal architecture processes and displays the status of the execution process. The message display is done by the DONE_IN_PROC which is predefined in the architecture. Though this is default to SQL Server, this makes a slight difference in performance. Obviously, when a query has lots of T-SQL statements in it to perform, this costs you a lot.

The DONE_IN_PROC message is pretty useful when we are using the query window to execute the procedure. But it runs unwanted when the web applications are concerned. Each operation of T-SQL statement will return this internal message (eg, an INSERT, UPDATE, or SET in a WHILE loop) for each step within the Stored Procedure execution cycle.

So from the web developer perspective, its quite unwanted when he update the files in to production server. Lets have a boost in performance this way.