Using pivot on datetime

Question

I have the following table:

Date,Type
2012年01月25日 13:48:00.000,1
2012年01月25日 13:49:00.000,2
2012年01月26日 13:48:00.000,1
2012年01月26日 13:49:00.000,2

I tried the pivot function to get this layout:

1,2
2012年01月25日 13:48:00.000,2012年01月25日 13:49:00.000
2012年01月26日 13:48:00.000,2012年01月26日 13:49:00.000

I'm not sure if it's even possible to use the pivot function, because I have to use an aggregate function in pivot, right? Is it possible to use pivot or should i use an self join?

Best regards Stefan

EDIT:

This is my pivot statement:

SELECT 
 [1],
 [2]
 FROM
 (
 SELECT
 [Date],
 [Type]
 FROM
 [Test]
 ) AS SourceTable
 PIVOT
 (
 SUM([Date]) FOR [Type] IN ([1],[2])
 ) AS PivotTable;

• Hannah Vernon: You should add the pivot you tried.
• vijayp: change the SUM to MAX, and check once
• user25186: That works, but when i have a second row in my table it won't be in the results.
• Lmu92: You might want to provide sample data that match your scenario (e.g. "a second row2 in your table)...
• user25186: @Lmu92 I added more sample data so that you know what i want to achieve. :)

Answer 1

I'd go with the classic Cross-Tab approach together with ROW_NUMBER to have "something" to group by:

;
WITH cte as
(
 SELECT ROW_NUMBER() OVER (PARTITION BY Type ORDER By Data) as pos, 
 * 
 FROM -- use your table name instead of the VALUES construct
 (VALUES
 ('2012-01-25 13:48:00.000' ,1),
 ('2012-01-25 13:49:00.000',2),
 ('2012-01-26 13:48:00.000',1),
 ('2012-01-26 13:49:00.000',2)) x(Data,Type)
)
SELECT 
MAX(CASE WHEN Type = 1 THEN Data ELSE NULL END) AS [1],
MAX(CASE WHEN Type = 2 THEN Data ELSE NULL END) AS [2]
FROM cte 
GROUP BY pos