如何在连接查询中应用数据透视？ [英] how to apply pivot in join query?

问题描述

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

推荐答案

你好Riyan，



你必须亲自尝试。昨天你会问类似的问题。我给了你解决方案。

现在你再次提出加入表格的相同问题。你可以用我昨天的解决方案做到这一点。在你自己尝试问问题之前。如果你得到结果然后问问题。但是看起来你没试过。



好​​了，我已经改变了我昨天的解决方案，为你的新要求。检查这个查询。

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

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