Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i have the below sql query:
SQL
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:
SQL
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

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

SQL
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


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..
Posted
Comments
Krunal Rohit 27-Feb-14 11:26am    
Have you tries using STUFF and XML PATH ?

-KR

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:
SQL
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:
SQL
INSERT INTO #TemporaryTable (<FieldsCollection>)
SELECT <YourQuery>
 
Share this answer
 
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[^]
 
Share this answer
 
Comments
Maciej Los 27-Feb-14 17:00pm    
Good advice, +5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900