0

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;
5
  • You should add the pivot you tried.
    – Hannah Vernon
    Oct 23, 2014 at 13:25
  • change the SUM to MAX, and check once
    – vijayp
    Oct 23, 2014 at 13:59
  • That works, but when i have a second row in my table it won't be in the results.
    – user25186
    Oct 23, 2014 at 14:01
  • You might want to provide sample data that match your scenario (e.g. "a second row2 in your table)...
    – Lmu92
    Oct 23, 2014 at 14:13
  • @Lmu92 I added more sample data so that you know what i want to achieve. :)
    – user25186
    Oct 23, 2014 at 14:17

1 Answer 1

0

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy