| Author |
 Topic  |
|
|
emyk
Starting Member
37 Posts |
 Posted - 10/09/2012 : 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
USA
15559 Posts |
Posted - 10/09/2012 : 12:38:12
|
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 |
 |
|
|
emyk
Starting Member
37 Posts |
Posted - 10/09/2012 : 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
USA
15559 Posts |
|
|
emyk
Starting Member
37 Posts |
Posted - 10/10/2012 : 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
|
Edited by - emyk on 10/10/2012 11:05:49 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 10/10/2012 : 11:03:51
|
| What's the full SQL statement? Need everything for CTEA including the WITH directive. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 10/10/2012 : 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
Starting Member
37 Posts |
Posted - 10/10/2012 : 11:47:42
|
Thank you!
That took care of it. Now I see my data's as expected.
|
|
|
| |
Topic |
|
|
|
SQL Server 2008 Pivot column
