SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Changing Table format		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnasz
Posting Yak Master

101 Posts
Posted - 04/20/2013 :  10:28:43  Show Profile  Reply with Quote
EmpID EmpName EntryDate Location totaltime
1234 test1 2013-04-08 Region1 00:00
1234 test1 2013-04-09 Region1 00:00
1234 test1 2013-04-10 Region1 06:47
1234 test1 2013-04-11 Region1 06:33
1234 test1 2013-04-12 Region1 06:22
1234 test1 2013-04-13 Region1 06:13
1235 test2 2013-04-08 Region2 00:00
1235 test2 2013-04-09 Region2 00:00
1235 test2 2013-04-10 Region2 06:47
1235 test2 2013-04-11 Region2 06:33
1235 test2 2013-04-12 Region2 06:22
1235 test2 2013-04-13 Region2 06:13

How do i change the table to this format


EmpID EmpName 2013-04-08 2013-04-09 2013-04-10
1234 test1 00:00 00:00 06:47
12345 test2 00:00 00:00 06:47

James K
Flowing Fount of Yak Knowledge

3764 Posts
Posted - 04/20/2013 :  12:55:09  Show Profile  Reply with Quote
quote:
Originally posted by pnasz

EmpID EmpName EntryDate Location totaltime
1234 test1 2013-04-08 Region1 00:00
1234 test1 2013-04-09 Region1 00:00
1234 test1 2013-04-10 Region1 06:47
1234 test1 2013-04-11 Region1 06:33
1234 test1 2013-04-12 Region1 06:22
1234 test1 2013-04-13 Region1 06:13
1235 test2 2013-04-08 Region2 00:00
1235 test2 2013-04-09 Region2 00:00
1235 test2 2013-04-10 Region2 06:47
1235 test2 2013-04-11 Region2 06:33
1235 test2 2013-04-12 Region2 06:22
1235 test2 2013-04-13 Region2 06:13

How do i change the table to this format


EmpID EmpName 2013-04-08 2013-04-09 2013-04-10
1234 test1 00:00 00:00 06:47
12345 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
Go to Top of Page

pnasz
Posting Yak Master

101 Posts
Posted - 04/20/2013 :  15:37:32  Show Profile  Reply with Quote
Yes there will be additional column. Till 2012-04-30
Go to Top of Page

pnasz
Posting Yak Master

101 Posts
Posted - 04/20/2013 :  15:41:03  Show Profile  Reply with Quote
Yes there will be additional column. Till 2012-04-30
Go to Top of Page

pnasz
Posting Yak Master

101 Posts
Posted - 04/21/2013 :  12:43:45  Show Profile  Reply with Quote
How to I show totaltime for pivot coloumn entrydate
Go to Top of Page

pnasz
Posting Yak Master

101 Posts
Posted - 04/21/2013 :  12:44:03  Show Profile  Reply with Quote
How to I show totaltime for pivot coloumn entrydate
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2230 Posts
Posted - 04/22/2013 :  01:13:17  Show Profile  Reply with Quote
Refer this link for totals with Pivoting
http://visakhm.blogspot.in/2012/04/display-total-rows-with-pivotting-in-t.html

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000