行到列也按分组和求和 [英] 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屋!
查看全文