Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query Help

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2013-05-08 : 22:43:06
Here is the sample data that i have


Name FILE Weekday COUNT
Shelb Receive 5/6/2013 Monday 104
Feli Receive 4/21/2013 Sunday 11
Shelb Send 4/23/2013 Tuesday 2
Jan Receive 4/27/2013 Saturday 12
Tris Send 5/4/2013 Saturday 3
Iri Receive 5/5/2013 Sunday 11
Shelb Receive 5/5/2013 Sunday 11
Lis Receive 4/25/2013 Thursday 92
Lar Send 5/2/2013 Thursday 122
Tony Receive 4/22/2013 Monday 134
Gracd Receive 5/5/2013 Sunday 6
Janet Receive 4/25/2013 Thursday 114
Franda Receive 4/24/2013 Wednesday 153
Johb Send 4/26/2013 Friday 25
Norc Receive 4/30/2013 Tuesday 144



I want end result would be like this

NAME,FILE,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY,
IRISP,SEND,count,count, count, count, count, count, count,
IRISP,RECEIVED,count,count, count, count, count, count, count,

All Users.....
I want to group by NAME,FILE,AND DATE, I want to create a report where i can compare all users Send and Receive Count. The data is only
two weeks. Weekday would be like this "date+weekday" and Count is already Counted How many he/she receive and Send per day/date.
Any help would be Great appreciate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 00:47:26
[code]
SELECT *
FROM
(
SELECT Name,File,
STUFF(Weekday,1,CHARINDEX(' ',Weekday),'') AS Weekday,
SUM([COUNT]) AS [COUNT]
FROM Table
GROUP BY Name,File
)m
PIVOT (SUM([COUNT]) FOR WeekDay IN ([Monday],[Tuesday],[Wednesday],[THURSDAY],[FRIDAY],[SATURDAY],[SUNDAY]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -