Cross Tab type Query

Below is a sample reference for dynamic columns (ie. u dont need to hard code 'Component' value in query)

Sample Data #tempA,#tempB

create Table #tempA (Empid bigint,EmpName VARCHAR(50))
INSERT INTO #tempA (Empid ,EmpName )VALUES(1,'Emp1')
INSERT INTO #tempA (Empid ,EmpName )VALUES(2,'Emp6')
INSERT INTO #tempA (Empid ,EmpName )VALUES(3,'Emp4')
INSERT INTO #tempA (Empid ,EmpName )VALUES(4,'Emp4')

create Table #tempB (id bigint ,Empid bigint,Component VARCHAR(50),Amount bigint)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(1,2,'Basic',10000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(2,2,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(3,2,'TA',750)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(4,1,'Basic',20000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(5,1,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(6,3,'Basic',6700)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(7,3,'Hra',1000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(8,4,'Basic',5000)
INSERT INTO #tempB (id,Empid ,Component,Amount )VALUES(9,4,'Hra',1000)

Combine Two Tables into #tempc

SELECT id,#tempB.Empid,EmpName,Component,Amount INTO #tempc
FROM #tempB
INNER JOIN #tempA ON #tempA.Empid=#tempB.Empid

Declare variables

DECLARE @cols NVARCHAR (MAX),
@selCols NVARCHAR (MAX),
@query  AS VARCHAR(MAX);

Fetch columns for dynamic query like [Basic],[Hra],[TA]

SELECT  @cols = COALESCE (@cols + ',[' + Component + ']', '[' + Component + ']') 
FROM    #tempB
group by Component

Fetch columns for Aliasing in dynamic query like [Basic] AS Basic,[Hra] AS Hra,[TA] AS TA

SELECT  @selCols = COALESCE (@selCols + ',[' + Component + ']', '[' + Component + ']') + ' AS '+ Component
FROM    #tempB
group by Component

Fetch columns for SUM in dynamic query like SUM(isnull(Basic,0)) AS Basic,SUM(isnull(Hra,0)) AS Hra,SUM(isnull(TA,0)) AS TA

SET @selCols=REPLACE(@selCols,'[','SUM(isnull(')
SET @selCols=REPLACE(@selCols,']',',0))')

Final Query

SET @query='SELECT Empid,EmpName,'+ @selCols+
		' FROM #tempc 
		PIVOT(
				SUM(Amount) 
				FOR Component 
				IN('+@cols+')		
				
		)AS empPivot
		group by Empid,EmpName'
execute(@query)		

good luck ;-)