SQL Server Forums
Profile | Register | 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
 SQL Server 2008 Pivot column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emyk
Yak Posting Veteran

50 Posts

Posted - 10/09/2012 :  12:03:36  Show Profile  Reply with Quote
I have been working on this for weeks now. So far I have not found a solution that works for me. So changed my original scope on this task.

So here is what I have now:
I have a list of names with a timestamp per names per day (as showed below), where I need to rotate the time column so that it shows the time for each names per day horizontal.

I am on SQL 2008 server and trying to see if PIVOT (Pivot by the time column) will accomplish the task here.

Any assistant is greatly appreciated.
(time)

NAME        ID         TYPE	   DATE		 TIME                    
Bob, Henry 1467157	Q  	2012-10-07	14:00                    
Bob, Henry 1467157	Q  	2012-10-07	22:19                    
Bob, Henry 1467157	Q  	2012-10-08	14:09                    
Bob, Henry 1467157	Q  	2012-10-08	22:05                    
Alex,Prince 1467157	Q  	2012-10-07	14:00                    
Alex,Prince 1467157	Q  	2012-10-07	22:19                    
Alex,Prince 1467157	Q  	2012-10-07	23:19                    
Alex,Prince 1467157	Q  	2012-10-07	23:29                    
Alex,Prince 1467157	Q  	2012-10-08	14:09                    
Alex,Prince 1467157	Q  	2012-10-08	22:05                    
                                                             
NAME        ID	     TYPE	   DATE          T1    T2    T3    T4 
Bob, Henry 1467157	Q  	2012-10-07	14:00 22:19              
Bob, Henry 1467157	Q  	2012-10-08	14:09 22:05              
Alex,Prince 1467157	Q  	2012-10-07	14:00 22:19 23:19 23:29  
Alex,Prince 1467157	Q  	2012-10-08	14:09 22:05              
Alex,Prince 1467157	Q  	2012-10-08	22:05                    

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/09/2012 :  12:38:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT NAME, ID, TYPE, [1] AS T1, [2] AS T2, [3] AS T3, [4] AS T4
FROM (SELECT NAME, ID, TYPE, DATE, TIME, 
ROW_NUMBER() OVER (PARTITION BY Name,ID,Type,Date ORDER BY Time) AS rownum
FROM myTable) a
PIVOT(MAX(Time) FOR rownum IN([1],[2],[3],[4])) b
Go to Top of Page

emyk
Yak Posting Veteran

50 Posts

Posted - 10/09/2012 :  13:52:27  Show Profile  Reply with Quote
thanks robvolk,

How do I make the columns dynamic? Can I do a COUNT on the MAX TIME and make my columns length be the MAX count? does this makes sense?

[1] AS T1, [2] AS T2, [3] AS T3, [4] AS T4
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/09/2012 :  14:04:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
See here: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

You can do COUNT() OVER and MAX() OVER to get those for each row, you would PARTITION BY the same way as the ROW_NUMBER() is partitioned.
Go to Top of Page

emyk
Yak Posting Veteran

50 Posts

Posted - 10/10/2012 :  10:50:58  Show Profile  Reply with Quote
I keep getting Incorrect syntax near ','.

Here is what I have


WITH
CTEA
AS
(
SELECT ..
FROM TABLE)
,

CTEB
AS
(
SELECT NAME,EmpID, rtyp,[1] AS T1, [2] AS T2, [3] AS T3, [4] AS T4 
FROM (NAME, EmpID, rtyp,Date,Time,
ROW_NUMBER() OVER (PARTITION BY NAME,EmpID, rtyp,Date ORDER BY Time) AS rownum
FROM CTEA
) a
PIVOT(MAX(Time) FOR rownum IN([1],[2],[3],[4])) b

Edited by - emyk on 10/10/2012 11:05:49
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/10/2012 :  11:03:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
What's the full SQL statement? Need everything for CTEA including the WITH directive.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/10/2012 :  11:09:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
Fix up this part:
CTEB
AS
(
SELECT NAME,EmpID, rtyp,[1] AS T1, [2] AS T2, [3] AS T3, [4] AS T4 
FROM (SELECT NAME, EmpID, rtyp,Date,Time,
ROW_NUMBER() OVER (PARTITION BY NAME,EmpID, rtyp,Date ORDER BY Time) AS rownum
FROM CTEA
) a
PIVOT(MAX(Time) FOR rownum IN([1],[2],[3],[4])) b)
SELECT * FROM CTEB
Go to Top of Page

emyk
Yak Posting Veteran

50 Posts

Posted - 10/10/2012 :  11:47:42  Show Profile  Reply with Quote
Thank you!
That took care of it. Now I see my data's as expected.





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.09 seconds. Powered By: Snitz Forums 2000