SQL Server 2008 Pivot column - 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

General SQL Server Forums

New to SQL Server Programming

SQL Server 2008 Pivot column

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-10-09 : 12:03:36

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

15732 Posts

Posted - 2012-10-09 : 12:38:12

[code]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[/code]

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-10-09 : 13:52:27

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-09 : 14:04:47

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.

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-10-10 : 10:50:58

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-10 : 11:03:51

What's the full SQL statement? Need everything for CTEA including the WITH directive.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-10 : 11:09:02

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

emyk
Yak Posting Veteran

57 Posts

Posted - 2012-10-10 : 11:47:42

Thank you!
That took care of it. Now I see my data's as expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources