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.