在sql数据库中将行转换为列 [英] Convert row into column in sql database

问题描述

我有以下sql查询：

i have the below sql query:

SELECT  evaluationPeriod.Name Period ,
        employeeEvaluation.ScoreResult Total,
        performanceArea.TypeLookupId,
        performanceArea.Name PA,
        employeeScore.ScoreInput INPUT,
        employeeScore.ScoreResult OUTPUT,
        employee.Name employeeName
FROM    dbo.APP_EvaluationPeriod evaluationPeriod
        INNER JOIN dbo.APP_EmployeeEvaluation employeeEvaluation ON evaluationPeriod.EvaluationPeriodId = employeeEvaluation.EvaluationPeriodId
        INNER JOIN dbo.APP_EmployeeScore employeeScore ON employeeEvaluation.EmployeeEvaluationId = employeeScore.EmployeeEvaluationId
        INNER JOIN dbo.APP_PerformanceArea performanceArea ON employeeScore.PerformanceAreaId = performanceArea.PerformanceAreaId
        INNER JOIN dbo.APP_PerformanceArea parentPerformanceArea ON performanceArea.ParentPerformanceAreaId = parentPerformanceArea.PerformanceAreaId
        INNER JOIN dbo.APP_Employee employee ON employee.EmployeeId = employeeEvaluation.EmployeeId

结果是：

the result is:

Period          Total TypeLookupId    PA         Input  Output  Employee
21-January-2014	NULL	302	      Warning	 4.000	70.000	ysf
21-January-2014	NULL	303	      Leaves	 0.000	70.000	ysf
21-January-2014	NULL	304	      Lateness   4.000	70.000	ysf
21-January-2014	NULL	305	      Others	 3.000	70.000	ysf
21-January-2014	NULL	306           MRAD	 8.000	50.000	ysf

这是一名员工ysf

如何将其更改为以下记录：

this is for one employee "ysf"
how can i change this into one record as below:

Period          Total WarningInput  WarningOutput LeavesInput LeavesOutput  Employee
21-January-2014 NULL	  4.000        70.000         0.000       70.000      ysf

and so on for all PAs

每个PA创建PAI


输入和PAOutput为每个员工获得一条记录

知道：每个PA的TypeLookupId是固定的（警告TypeLookupId是302，对于Leaves TypeLookupId是303 ...）



谢谢..

for each PA create PAInput and PAOutput to get one record for each employee
KNOWING THAT: TypeLookupId for each PA is fixed (for warning TypeLookupId is 302, for Leaves TypeLookupId is 303 ... )

Thanks..

推荐答案

通过mgoad99 [ ^ ]，因为使用pivot是获得所需内容的最佳方式。



另外，你可以使用结束时的情况 [ ^ ]声明。

看看例子：

Have a look at solution 1 by mgoad99[^], because using pivot is the best way to achieve what you need.

Alternatively, you can use CASE WHEN END[^] statement.
Have a look at example:

CREATE TABLE #tmp (Period DATETIME, Total INT NULL, TypeLookupId INT, PA VARCHAR(30), [Input] DECIMAL(8,4), [Output] DECIMAL(8,4), Employee VARCHAR(30))

INSERT INTO #tmp (Period, Total, TypeLookupId, PA, [Input], [Output], Employee)
VALUES('21-January-2014', NULL, 302, 'Warning', 4.000, 70.000, 'ysf'),
('21-January-2014', NULL, 303, 'Leaves', 0.000, 70.000, 'ysf'),
('21-January-2014', NULL, 304, 'Lateness', 4.000, 70.000, 'ysf'),
('21-January-2014', NULL, 305, 'Others', 3.000, 70.000, 'ysf'),
('21-January-2014', NULL, 306, 'MRAD', 8.000, 50.000, 'ysf')

SELECT Period, SUM(WarningInput) + SUM(WarningOutput) + SUM(LeavesInput) + SUM(LeavesOutput) AS Total,
    SUM(WarningInput) AS WarningInput, SUM(WarningOutput) AS WarningOutput, SUM(LeavesInput) AS LeavesInput, SUM(LeavesOutput) AS LeavesOutput, Employee
FROM (
    SELECT Period, CASE WHEN TypeLookupId=302 THEN Input END AS 'WarningInput',
        CASE WHEN TypeLookupId=302 THEN Output END AS 'WarningOutput',
        CASE WHEN TypeLookupId=303 THEN Input END AS 'LeavesInput',
        CASE WHEN TypeLookupId=303 THEN Output END AS 'LeavesOutput', Employee
    FROM #tmp
) AS T
GROUP BY Period, Employee

DROP TABLE #tmp

在这种情况下，您可以使用以下方法声明临时表并将数据插入其中：

In this case you can declare temporary table and insert data into it using:

INSERT INTO #TemporaryTable (<FieldsCollection>)
SELECT <YourQuery>

在SQL中使用Pivot运算符可以为您提供所需的内容。



http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx [ ^ ]

Using the Pivot operator in SQL should give you what you are looking for.

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

这篇关于在sql数据库中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！