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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.