如何在SQL中使用pivot作为日期列 [英] How do I use pivot in SQL for date column

问题描述

我有工作人员和评估日期的表格我希望将该日期作为date1，date2和日期3进行调整。



我尝试了什么：

i have table with staffid and evaluation date i wants to pivot that date as date1,date2 and date 3.

What I have tried:

select * from 
(  select staffid, effectivedate  from EvaluationDetail) src
pivot
(  avg(staffid)  for effectivedate in ([effectivedate1], [effectivedate2], [effectivedate3]) ) piv;

推荐答案

略微猜测关于如何填充列，尝试这样的事情：

Guessing slightly on how you want to populate the columns, try something like this:

WITH cteOrderedDates As
(
    SELECT
        StaffId,
        EffectiveDate,
        ROW_NUMBER() OVER (PARTITION BY StaffId ORDER BY EffectiveDate DESC) As RN
    FROM
        EvaluationDetail As D
)
SELECT
    StaffId,
    MAX(CASE RN WHEN 3 THEN EffectiveDate END) As EffectiveDate1,
    MAX(CASE RN WHEN 2 THEN EffectiveDate END) As EffectiveDate2,
    MAX(CASE RN WHEN 1 THEN EffectiveDate END) As EffectiveDate3
FROM
    cteOrderedDates
WHERE
    RN <= 3
GROUP BY
    StaffId
;

您必须枚举您想要包含的日期，例如

You have to enumerate the dates you want to include e.g.

select * from 
(  select staffid, effectivedate  from #EvaluationDetail) src
pivot
(  count(staffid)  for effectivedate in ([2018-01-10], [2018-03-10], [2018-02-05]) ) piv;

注意我已将函数更改为 count 因为一个staffid的平均值意味着什么都没有。



我假设列 effectivedate 被声明为类型 date - 特别是 datetime ！



如果您希望数据本身确定日期列表，那么您将需要一些动态SQL。

Note I've changed the function to count because the average of a staffid means nothing at all.

I've assumed that the column effectivedate is declared as type date - specifically not datetime!

If you want that list of dates to be determined by the data itself then you will need some dynamic SQL.

这篇关于如何在SQL中使用pivot作为日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！