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.
| Author |
Topic |
|
pnasz
Posting Yak Master
101 Posts |
 Posted - 2013-04-20 : 10:28:43
|
| EmpID EmpName EntryDate Location totaltime1234 test1 2013-04-08 Region1 00:001234 test1 2013-04-09 Region1 00:001234 test1 2013-04-10 Region1 06:471234 test1 2013-04-11 Region1 06:331234 test1 2013-04-12 Region1 06:221234 test1 2013-04-13 Region1 06:131235 test2 2013-04-08 Region2 00:001235 test2 2013-04-09 Region2 00:001235 test2 2013-04-10 Region2 06:471235 test2 2013-04-11 Region2 06:331235 test2 2013-04-12 Region2 06:221235 test2 2013-04-13 Region2 06:13How do i change the table to this format EmpID EmpName 2013-04-08 2013-04-09 2013-04-101234 test1 00:00 00:00 06:4712345 test2 00:00 00:00 06:47 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-20 : 12:55:09
|
quote:
Originally posted by pnasz
EmpID EmpName EntryDate Location totaltime1234 test1 2013-04-08 Region1 00:001234 test1 2013-04-09 Region1 00:001234 test1 2013-04-10 Region1 06:471234 test1 2013-04-11 Region1 06:331234 test1 2013-04-12 Region1 06:221234 test1 2013-04-13 Region1 06:131235 test2 2013-04-08 Region2 00:001235 test2 2013-04-09 Region2 00:001235 test2 2013-04-10 Region2 06:471235 test2 2013-04-11 Region2 06:331235 test2 2013-04-12 Region2 06:221235 test2 2013-04-13 Region2 06:13How do i change the table to this format EmpID EmpName 2013-04-08 2013-04-09 2013-04-101234 test1 00:00 00:00 06:4712345 test2 00:00 00:00 06:47
I am not able to correlate between your input data and the output. Won't you have additional columns for 2013-04-11, 2013-04-12 etc.? Assuming you do, what you will need to do is to pivot the source data. If you have only a known number of dates, and if you know them in advance, you can use static pivot. If you don't, which probably is the case, you will need to use dynamic pivot. See Madhivanan's blog for code and example here: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx |
 |
|
|
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-20 : 15:37:32
|
| Yes there will be additional column. Till 2012-04-30 |
|
|
|
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-20 : 15:41:03
|
| Yes there will be additional column. Till 2012-04-30 |
|
|
|
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-21 : 12:43:45
|
| How to I show totaltime for pivot coloumn entrydate |
|
|
|
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-21 : 12:44:03
|
| How to I show totaltime for pivot coloumn entrydate |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
|
|
|
Changing Table format