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>