Menu
I have table with values as follows -
EMP_CODE | LEAVENAME | APP_TYPE | LEAVE_DATE | ACT_DAYS
--------------------------------------------------------
ST006 | CL | P | 2012-01-03 | 1.0
ST006 | CL | P | 2012-01-18 | 1.0
ST006 | SL | P | 2012-01-27 | 1.0
ST002 | CL | P | 2012-01-04 | 1.0
ST002 | CL | P | 2012-01-12 | 1.0
ST002 | SL | P | 2012-01-27 | 1.0
OCO038 | CL | P | 2012-01-27 | 1.0
HO188 | CL | P | 2012-01-09 | 1.0
HO188 | CL | P | 2012-01-30 | 1.0
HO085 | CL | P | 2012-01-19 | 1.0
HO085 | SL | P | 2012-01-23 | 1.0
I have written this query to sum all leave types as columns for each employee. Each employee must have only one row.
SELECT EMP_CODE,[CL],[LWP],[PL],[SL] FROM LEAVE_DETAIL L
PIVOT (SUM(ACT_DAYS) FOR LEAVENAME IN ([CL],[LWP],[PL],[SL]))
AS PVT ORDER BY EMP_CODE;
But this query is not giving me the expected output. There are more than one row for each employee which is not what I want.
The following table show the expected output -
EMP_CODE | CL | SL |
---------|------|-----|
ST006 | 2.0 | 1.0 |
ST002 | 2.0 | 1.0 |
OCO038 | 1.0 | 0.0 |
HO188 | 2.0 | 0.0 |
HO085 | 1.0 | 1.0 |
Please help.
223
In this article, I am going to explain how we can create a dynamic pivot table in SQL Server. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from the vast datasets.
The answer is no: PIVOT requires aggregation.
Group a Pivot Table by Date. Right-click a cell within a row or column field containing dates and select Group... ...or on the PivotTable Tools | Analyze tab, in the Group group, click the Group Field button. The Grouping dialog is invoked.
GROUPING SETS are groups, or sets, of columns by which rows can be grouped together. Instead of writing multiple queries and combining the results with a UNION, you can simply use GROUPING SETS. GROUPING SETS in SQL can be considered an extension of the GROUP BY clause.
You don't even need the group by in the query. Because what a pivot does is that it "group by" on the other columns. The key to this solution is the inner select. I think it is not a god idé to first do a sum with group by and then apply a sum and a group by again.
SELECT
EMP_CODE,
[CL],
[LWP],
[PL],
[SL]
FROM
(
SELECT
EMP_CODE,
LEAVENAME,
ACT_DAYS
FROM
@tmp_emp
) L
PIVOT
(
SUM(ACT_DAYS)
FOR LEAVENAME IN ([CL],[LWP],[PL],[SL])
)
AS PVT
ORDER BY EMP_CODE
This will get you the same result.
81
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
