how to apply pivot in join query?

Hi Riyan,

You have to try by your self.yesterday you ask the similar question .I gave you the solution.
Now again you are asking the same question with Join tables.You can do that with my yesterday solution.before asking quesiotns try by your self .if you cont get the result then ask question .But it seems you didnt try that.

Ok here i have alter my yesterday solution for your new requirment.Check this query.

-- Create Table
Create Table EmpDetails
(
Emp_Id  int,
Emp_Name varchar(40),
Allowance_Names varchar(40),
Allowance_Values varchar(40),
)
--Sample data Insert
Insert into EmpDetails Values(1,'Emp1',     'AllowNames1',         'Values1')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames2',         'Values2')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames3',         'Values3')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames4',         'Values4')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames1',         'Values2_1')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames2',         'Values2_2')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames3',         'Values2_3')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames4',         'Values2_4')


create table EmpSalary(
Emp_Id  int,
Allowance_Names varchar(40),
Allowance_Value int
)
--Sampel Insert Query
insert into EmpSalary values(1,'AllowNames1',500 )
insert into EmpSalary values(1,'AllowNames1',47 )
insert into EmpSalary values(1,'AllowNames2',400 )
insert into EmpSalary values(1,'AllowNames3',700 )
insert into EmpSalary values(1,'AllowNames4',45 )
insert into EmpSalary values(2,'AllowNames2',50 )
insert into EmpSalary values(2,'AllowNames3',340 )
insert into EmpSalary values(2,'AllowNames4',145 )


-- here is your pivot query
-- Pivot Select Query
DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Allowance_Names) 
                    FROM EmpDetails
                    GROUP BY Allowance_Names
                    ORDER BY Allowance_Names
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @SQLquery = N'SELECT Emp_Id,Emp_Name,' + @MyColumns + N' from 
             (
               SELECT 
                A.Emp_Id as Emp_Id,
				A.Emp_Name as Emp_Name, 
				B.Allowance_Value as values2, 
				B.Allowance_Names as Allowance_Names
    FROM EmpDetails A Inner JOIN EmpSalary B
    ON A.Emp_Id=B.Emp_Id
    AND A.Allowance_Names=B.Allowance_Names
            ) x
            pivot 
            (
                 SUM(values2)
                for Allowance_Names in (' + @MyColumns + N')
            ) p '
exec sp_executesql @SQLquery;

The Final output will be like this

1	Emp1	547	400	700	45
2	Emp2	NULL	50	340	145