Column To row - SQL Server Forums

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

Transact-SQL (2005)

Column To row

Author

Topic

pssumesh2003
Starting Member

35 Posts

Posted - 2010-02-16 : 00:46:39

Hi All,

I have a table to store timesheet,table structure is given below

Id ,weeKNo,SiteId,EmPId,S,Su,M,T,W,Th,F,Wage,totalWage,Advance,Extra

Here Week No is an integer,S,Su,M,T,W,Th,F numeric(18,0) to store No of Work done by Employ of Id (EmpId) in Site (SiteId) for S(Saturday),Su(Sunday),---F(friday) of the Week (WeekNo) . For WeekNo=1
S was 2/1/2010(dd/MM/yyyy).

Now i want a report for a Site Like

Date Work
2/1/2010 25.5
4/1/2010 10
5/1/2010 12
6/1//2010 15
.
.
15/2/2010 20

where Date Corespond(S,...F) Work Sum(S)..Sum(F) groupby SiteId,weekno

How will get this in a select query?
Please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 00:50:21

does weekno represent week number of year or is it week within month?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pssumesh2003
Starting Member

35 Posts

Posted - 2010-02-16 : 01:00:23

WeekNo represents WeekNo in a year. Every saturday it incriments by 1

i think date for S can calculate like
dateadd(dd,(WeekNo*7+0),'2/1/2010').
my problem is how can query create

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 01:23:36

quote:
Originally posted by pssumesh2003

WeekNo represents WeekNo in a year. Every saturday it incriments by 1

i think date for S can calculate like
dateadd(dd,(WeekNo*7+0),'2/1/2010').
my problem is how can query create

this should give you a start


SELECT weeKNo,SiteId,EmPId,Work,Days
FROM
(SELECT weeKNo,SiteId,EmPId,S,Su,M,T,W,Th,F FROM Yourtable) t
UNPIVOT(Work FOR Days IN ([S],[Su],[M],[T],[W],[Th],[F]))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pssumesh2003
Starting Member

35 Posts

Posted - 2010-02-16 : 01:38:15

Thank u Very nuch visakh16.

This is the solution i really want.
once again thank u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 02:11:10

welcome