行到列也按分组和求和 [英] rows to columns also group by and sum

问题描述

大家好，



我有以下样本数据： -

Hi all,

I have below sample data: -

EmpCode	Duty_Date	Status
================================
5001	01-Mar-12	Present
5002	02-Mar-12	Off
5003	01-Mar-12	Absent
5004	04-Mar-12	Leave
5001	02-Mar-12	Present
5002	05-Mar-12	Present
5001	03-Mar-12	Off
5005	01-Mar-12	Absent
5003	02-Mar-12	Present
5005	02-Mar-12	Leave
5001	04-Mar-12	Present
5003	03-Mar-12	Present
5001	05-Mar-12	Leave
5005	03-Mar-12	Absent
5005	05-Mar-12	Present
5003	05-Mar-12	Present
5005	04-Mar-12	Absent
5002	01-Mar-12	Leave
5002	03-Mar-12	Absent
5002	04-Mar-12	Present
5003	04-Mar-12	Present
5004	01-Mar-12	Present
5004	02-Mar-12	Absent
5004	03-Mar-12	Leave
5004	05-Mar-12	off
-----------------------------------

现在，我希望状态为EmpCode-Duty_Date-wise以及状态明智的行总和。 />
最后输出应如下：

Now, I want the status EmpCode-wise Duty_Date-wise along with status wise row sum.
Finally output should be as below:

EmpCode	Day-1	Day-2	Day-3	Day-4	Day-5	….	Day-30	Preset	Absent	Leave	Off

5001	Present	Present	Off	Present	Leave	….	Day-30	3	0	1	1
5002	Leave	Off	Absent	Present	present	….	Day-30	2	1	1	1
5003	Absent	Present	Present	Present	present	….	Day-30	4	1		
5004	Present	Absent	Leave	Leave	Off	….	Day-30	1	1	2	1
5005	Absent	Leave	Absent	Absent	present	….	Day-30	1	3	1	
---------------------------------------------------------------------------------------------

请帮助我。

我使用的是SQL Server 2008



问候，



Anil



代码块添加 - OriginalGriff [/ edit]

Kindly help me.
I am using SQL Server 2008

Regards,

Anil

[edit]Code block added - OriginalGriff[/edit]

推荐答案

您想要转动。 DAY上的一个支点（duty_date）会给你想要的。当您确切知道要分组的值时，透视非常有用。





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



如果您提供SQL来创建数据并将数据插入到表中，我将为您编写SQL。

You want to pivot. A pivot on DAY(duty_date) will give you want you want. Pivot is useful when you know exactly the values you want to group by.


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

If you provide SQL to create and insert data in to your table, I will write the SQL for you.

这篇关于行到列也按分组和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！