rows to columns also group by and sum

Question

3.40/5 (2 votes)

See more:

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
-----------------------------------

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	
---------------------------------------------------------------------------------------------

Kindly help me.
I am using SQL Server 2008

Regards,

Anil

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

Posted 11-Jan-14 21:01pm

anil_kumar_bhakta

Updated 11-Jan-14 21:26pm

OriginalGriff

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2014-01-12T10:50:00

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.