SQL Server ..查询以在一行上获取每个Employee&＃结果 [英] SQL Server.. Query to get each Employee's Results on the one line

问题描述

我有这个查询，其中包含我需要的所有信息。但我不完全确定如何让它显示我想要它的方式..我的结果集每分钟为每个PayComponent采取一个新的行。因此，员工可能会在此
结果集中插入100次。有没有办法可以在一行中显示所有细节？

I have this query which has all the information that i need. But im not entirely sure how to get it to display the way i want it to.. At the minute my result set is taking a new line per PayComponent. So an employee could potentially be inserted in this result set 100 times. Is there a way i can display all the details in the one line?

这是我的代码：

IF OBJECT_ID('dbo.PCompExceptionReport', 'U') IS NOT NULL
Drop Table PCompExceptionReport

CREATE TABLE PCompExceptionReport
(
EeID INT,
EeRef VARCHAR(10),
FirstName VARCHAR(50),
Surname VARCHAR(50),
PayCompID SMALLINT,
PayCompDesc VARCHAR(50),
RateLastMonth MONEY,
RateThisMonth MONEY
)

INSERT INTO PCompExceptionReport (EeID, EeRef, FirstName, Surname, PayCompID, PayCompDesc, RateLastMonth)
SELECT
ed.EeID,
ed.EeRef,
ed.Forename,
ed.Surname,
eec.PCompID,
eec.PCDescr,
eec.Rate
FROM EeDetails ed
INNER JOIN UnityEeComponents eec ON eec.EeRef = ed.EeRef
CROSS JOIN PayrollRuns pr
WHERE pr.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = '2019-04-30')
ORDER BY ed.EeID, PCompID

UPDATE PCompExceptionReport
SET PCompExceptionReport.RateThisMonth = ECRate
FROM
EeComponents ec
WHERE ec.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = '2019-05-31') AND PCompExceptionReport.PayCompID = ec.PCompID AND PCompExceptionReport.EeID = ec.EeID

SELECT 
CONCAT(YEAR(GETDATE()), '-', MONTH(GETDATE()))AS 'PayMonth',
'' AS 'PayGroup',
pexc.EeRef AS 'EmployeeID',
pexc.Surname + ' ' + LEFT(pexc.Firstname,1) + '.' AS 'Name',
CASE WHEN ed.DeptID = '1' THEN '[Default]' 
	WHEN ed.DeptID = '2' THEN 'Enterprise Corp Legal GBR' 
	WHEN ed.DeptID = '3' THEN 'Enterprise Corp Services' 
	WHEN ed.DeptID = '4' THEN 'Enterprise Finance/Acctg'
	WHEN ed.DeptID = '5' THEN 'Enterprise Human Resource'
	WHEN ed.DeptID = '6' THEN 'Enterprise Marketing GBR'
	WHEN ed.DeptID = '7' THEN 'Global Technology Service'
	WHEN ed.DeptID = '8' THEN 'Unified Communications Sr'
	WHEN ed.DeptID = '9' THEN 'West Digital Media Soluti'
END AS 'Department',
PayCompID,
pexc.PayCompDesc AS 'PayComponent',
pexc.RateLastMonth AS 'Last Month',
pexc.RateThisMonth AS 'Last Month',
CASE WHEN pexc.RateLastMonth > pexc.RateThisMonth THEN pexc.RateLastMonth - pexc.RateThisMonth 
     WHEN pexc.RateThisMonth > pexc.RateLastMonth THEN pexc.RateThisMonth - pexc.RateLastMonth
	 ELSE '0.00' END AS 'Variance'
FROM PCompExceptionReport pexc
INNER JOIN EeDetails ed ON ed.EeID = pexc.EeID
ORDER BY pexc.EeRef, pexc.PayCompID

这可以得到我需要的一切，但只是没有按照我想要的方式显示。我不确定我的询问是否可能，但任何帮助或建议将不胜感激。

This gets everything i need, but just not displayed the way i would like. Im not sure if what im asking is even possible but any help or suggestions would be greatly appreciated.

这是我目前的结果：

从EmployeeID可以看出这是一样的员工，有什么方法可以将所有这些薪酬成分及其价值放在每个员工的一行上吗？

As you can see from the EmployeeID this is the same employee, is there any way i can put all those paycomponents and their values on the one line per employee?

推荐答案

您可以使用PIVOT运营商，请参阅
FROM - 使用PIVOT和UNPIVOT

You can by using the PIVOT Operator, see FROM - Using PIVOT and UNPIVOT

这篇关于SQL Server ..查询以在一行上获取每个Employee&＃结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！