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.
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
--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.
