如何在连接查询中应用数据透视？

问题描述

SELECT     dbo.Employee_Master.Name, dbo.Employee_Salary.Emp_id, dbo.AllowanceDeduce_Master.Name AS AllowanceName, dbo.Employee_Salary.Allowance_Value
FROM         dbo.Employee_Salary INNER JOIN
                      dbo.AllowanceDeduce_Master ON dbo.AllowanceDeduce_Master.AllowDCode = dbo.Employee_Salary.Allowance_Code INNER JOIN
                      dbo.Employee_Master ON dbo.Employee_Master.EMPID = dbo.Employee_Salary.Emp_id

Sir这是我的代码，如何在AllowanceName中应用数据透视，Allowance_Value

我需要这样出来，任何人都可以帮我，我该怎么做？

Sir This my code , how can i apply pivot in AllowanceName,Allowance_Value
I need out put like this, can any one help me how can i do this?

Emp_Id  Emp_Name AllowanceName1  AllowanceName2.... 
------ -------   --------------- -------------------
101     john     1000.00           350.00

推荐答案

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

这篇关于如何在连接查询中应用数据透视？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！