SQL Server 2008 R2 – Using Pivot on Datetime

pivotsql-server-2008-r2

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;

Best Answer

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

Related Solutions

Sql-server – Best way to index a table with two datetime columns that are equally queried

There is no substitute for benchmarking. To answer the question, I would create and populate several possible tables. Then I would expose these tables to your typical workload, and benchmark.

Including additional columns in you NCIs will slow down modifications and speed up selects. Based on frequency of both, we can choose which approach uses less resources. If a row is on average read twice per year, your conclusions might be different as compared to the case when every row is on average read twice per minute.

Besides, not all queries are born equal. If some queries must complete in certain time no matter what, then you must make sure these requirements are met. Obviously such requirement trump the common good approach described above. Only you can know the actual requirements.

Sql-server – Help with a PIVOT

You are on the right track with your current query but I would alter it slightly to use a subquery and include a row_number() to give a distinct value to each score in your rank.

If you query your data using:

select [rank], score,
  row_number() over(partition by [rank] order by [rank]) rn
from yourtable

See Demo

You will get a sequenced number for each score inside your ranks. This sequence number will then be grouped by in your PIVOT making your full code:

select [1], [2], [3]
from
(
  select [rank], score,
    row_number() over(partition by [rank] order by [rank]) rn
  from yourtable
) d
pivot
(
  max(score)
  for [rank] in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This query gives you a result:

| 1 |   2 | 3 |
|---|-----|---|
| 1 | 2.5 | 5 |
| 7 | 2.5 | 8 |
| 9 | 2.5 | 5 |

Best Answer

Related Solutions

Sql-server – Best way to index a table with two datetime columns that are equally queried

Sql-server – Help with a PIVOT

Related Question