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

Using Temporary Table, SQL Server 2005

ALTER PROCEDURE dbo.BUDGET_COMMITMENT_QTY_REPORT

(

@BudgetYear NVARCHAR(50) = null,

@BudgetVersion NVARCHAR(50) = null,

@Company NVARCHAR(50) = null,

@Division NVARCHAR(50) = null,

@CostCenter NVARCHAR(50) = null,

@SubAccount NVARCHAR(50) = null,

@SalesDivision NVARCHAR(50) = null,

@AsOnDate DATETIME = null

)

AS

SET NOCOUNT ON

BEGIN

DECLARE @Select NVARCHAR(4000)

DECLARE @Param NVARCHAR(4000)

SET @Param = ' @BudgetYear NVARCHAR(50) = null,

@BudgetVersion NVARCHAR(50) = null,

@Company NVARCHAR(50) = null,

@Division NVARCHAR(50) = null,

@CostCenter NVARCHAR(50) = null,

@SubAccount NVARCHAR(50) = null,

@SalesDivision NVARCHAR(50) = null,

@AsOnDate DATETIME = null '

CREATE TABLE #TEMPTABLE

(

BudgetType NVARCHAR(50), BudgetYear NVARCHAR(50), Company NVARCHAR(50), Division NVARCHAR(50), SalesDivision NVARCHAR(50), Costcenter NVARCHAR(50), SubAccount NVARCHAR(50), Display NVARCHAR(50), Amount DECIMAL(29,2), Quantity DECIMAL(29,2), RQDate DateTime

)

INSERT INTO #TEMPTABLE

SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS year, COMPANY_CODE AS Company, DIVISION_CODE AS Division,

SALES_DIVISION AS SalesDivision, COST_CENTER AS costCenter, SUB_ACCOUNT_CODE AS subAccount, 'Precommitment' AS Display, SUM(AMOUNT)

AS TotalValue, SUM(QUANTITY) AS Quantity, PR_REQ_DATE AS RQDate

FROM ECMS_PR_BUDGET_CHECK

GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, QUANTITY, DIVISION_CODE,

PR_REQ_DATE

UNION ALL

SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS BudgetYear, COMPANY_CODE AS Company, DIVISION_CODE AS Division,

SALES_DIVISION AS SalesDivision, COST_CENTER AS CostCenter, SUB_ACCOUNT_CODE AS SubAccount, 'Commitment' AS Display, SUM(AMOUNT)

AS TotalValue, SUM(QUANTITY) AS Quantity, PO_REQ_DATE AS RQDate

FROM ECMS_PO_BUDGET_CHECK

GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, DIVISION_CODE, PO_REQ_DATE

--SELECT * FROM #TEMPTABLE

SET @Select = 'SELECT #TEMPTABLE.BudgetYear, #TEMPTABLE.Display, #TEMPTABLE.Amount,

#TEMPTABLE.Quantity, ECMS_MST_COMPANY.Company_Name, ECMS_MST_COST_CENTER.Cost_center_desc,

ECMS_MST_SUB_ACCOUNT.SUB_AC_NAME, ECMS_MST_SALES_DIVISION.SALES_DIVISION_NAME , ECMS_MST_DIVISION.DIVISION_NAME

FROM #TEMPTABLE

INNER JOIN

ECMS_MST_BUDGET_TYPE ON #TEMPTABLE.BudgetType = ECMS_MST_BUDGET_TYPE.BUDGET_CODE INNER JOIN

ECMS_MST_COMPANY ON #TEMPTABLE.Company = ECMS_MST_COMPANY.Company_Code INNER JOIN

ECMS_MST_COST_CENTER ON #TEMPTABLE.Costcenter = ECMS_MST_COST_CENTER.Cost_center_code INNER JOIN

ECMS_MST_SUB_ACCOUNT ON #TEMPTABLE.SubAccount = ECMS_MST_SUB_ACCOUNT.SUB_AC_CODE INNER JOIN

ECMS_MST_SALES_DIVISION ON #TEMPTABLE.SalesDivision = ECMS_MST_SALES_DIVISION.SALES_DIVISION_CODE INNER JOIN

ECMS_MST_DIVISION ON #TEMPTABLE.Division = ECMS_MST_DIVISION.DIVISION_CODE '

IF @BudgetYear<>0 AND @BudgetYear IS NOT NULL

SET @Select = @Select+ ' AND (BudgetYear=@BudgetYear) '

IF @BudgetYear IS NOT NULL

SET @Select = @Select+ ' AND (BudgetType=@BudgetVersion) '

IF @Company<>0 AND @Company IS NOT NULL

SET @Select = @Select+ ' AND (Company=@Company) '

IF @Division<>0 AND @Division IS NOT NULL

SET @Select = @Select+ ' AND (Division=@Division) '

IF @SalesDivision<>0 AND @SalesDivision IS NOT NULL

SET @Select = @Select+ ' AND (SalesDivision=@SalesDivision) '

IF @Costcenter<>0 AND @Costcenter IS NOT NULL

SET @Select = @Select+ ' AND (Costcenter=@Costcenter) '

IF @SubAccount<>0 AND @SubAccount IS NOT NULL

SET @Select = @Select+ ' AND (SubAccount=@SubAccount) '

IF @AsOnDate<>0 AND @AsOnDate IS NOT NULL

SET @Select = @Select+ ' AND (RQDate<=@AsOnDate) '

--print @Select

Execute sp_Executesql @Select, @Param , @BudgetYear, @BudgetVersion, @Company, @Division, @CostCenter, @SubAccount, @SalesDivision, @AsOnDate

END

SET NOCOUNT OFF

RETURN