sql2000 - row changed to diff form? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

sql2000 - row changed to diff form?

Author

Topic

Akshaya Pargunan
Starting Member

10 Posts

Posted - 2011-10-11 : 06:58:55

my date in database
Monday Tuesday Wednesday Thursday Friday Saturday Sunday WeekEndDate
------ ------- --------- -------- ------ -------- ------ -----------
2 0 0 0 5 8 0 02/10/2011
0 3 0 0 0 0 0 09/10/2011
3 0 2 0 0 0 0 16/10/2011
0 0 0 1 0 8 9 23/10/2011
0 0 0 0 0 6 0 30/10/2011

i have to generate result like below
the [date of request] (having values,remove all the 0 entries) in the first column based on weekenddate and the correspoding values on the second column [hoursused]

dateofrequest hoursused
----------------- --------------
26/09/2011 2
30/09/2011 5
01/10/2011 8
04/10/2011 3
10/10/2011 3
12/10/2011 2
20/10/2011 1
22/10/2011 8
23/10/2011 9
28/10/2011 6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 07:15:15

select WeekEndDate - 6, Monday where Monday > 0
union all
select WeekEndDate - 5, Tuesday where Tuesday > 0
union all
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Akshaya Pargunan
Starting Member

10 Posts

Posted - 2011-10-11 : 08:56:16

Thanks a lot for the information
it helped me lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources