按日期名称转换列中的行

vddsk6oq 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(254)

我有这张table

|ID | day_name   |        day_date     |
  1   Monday     2018-01-08 00:00:00.000
  2   Monday     2018-01-15 00:00:00.000
  3   Monday     2018-01-22 00:00:00.000
  4   Monday     2018-01-29 00:00:00.000
 10   Tuesday    2018-01-16 00:00:00.000
 11   Tuesday    2018-01-23 00:00:00.000
 12   Tuesday    2018-01-30 00:00:00.000

我想将行旋转为列，结果集如下所示：

|        Monday            |       Tuesday           |
2018-01-08 00:00:00.000     2018-01-16 00:00:00.000
2018-01-15 00:00:00.000     2018-01-23 00:00:00.000
2018-01-22 00:00:00.000     2018-01-30 00:00:00.000
2018-01-29 00:00:00.000

我尝试了一些东西，但结果并不像预期的那样：

WITH dayz AS
(
    SELECT day_name, day_date,
    [rn] = RANK() OVER (PARTITION BY day_name ORDER BY day_date)
    FROM ISP_Cloud_DaysFromSession
)
SELECT 
    day_name,
     MondayDates = MAX(CASE WHEN rn = 1 THEN day_date ELSE NULL END),
  TuesdayyDates = MAX(CASE WHEN rn = 2 THEN day_date ELSE NULL END),
  Column3 = MAX(CASE WHEN rn = 3 THEN day_date ELSE NULL END),
  Column4 = MAX(CASE WHEN rn = 4 THEN day_date ELSE NULL END),
  Column5 = MAX(CASE WHEN rn = 5 THEN day_date ELSE NULL END),
  Column6 = MAX(CASE WHEN rn = 6 THEN day_date ELSE NULL END),
  Column7 = MAX(CASE WHEN rn = 7 THEN day_date ELSE NULL END),
  Column8 = MAX(CASE WHEN rn = 8 THEN day_date ELSE NULL END),
  Column9 = MAX(CASE WHEN rn = 9 THEN day_date ELSE NULL END)
  FROM
  dayz
  GROUP BY day_name

有什么办法可以改变这种状况吗？

sql sql-server multiple-columns rows

来源：https://stackoverflow.com/questions/61887937/transform-rows-in-columns-by-day-name

2条答案

按热度按时间

ljo96ir51#

你离得不远，但你的分组方式不对。行号是您在每一行中想要的，但是您已经将它定义为您的列

WITH Days AS
    (SELECT day_name,
            day_date,
            RANK() OVER (PARTITION BY day_name ORDER BY day_date) AS [rn]
     FROM dbo.ISP_Cloud_DaysFromSession)
SELECT MAX(CASE day_name WHEN 'Monday' THEN day_date END) AS Monday,
       MAX(CASE day_name WHEN 'Tuesday' THEN day_date END) AS Tuesday
FROM Days
GROUP BY rn
ORDER BY rn ASC;

赞(0）回复(0）举报 2021-08-09

hpxqektj2#

你也可以使用 row_number 以及 pivot :

WITH Days AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY day_name ORDER BY day_date) rr
    , day_name, day_date
  FROM ISP_Cloud_DaysFromSession
)
SELECT [Monday], [Tuesday]
FROM (
  SELECT *
  FROM Days
) AS SOURCE
PIVOT (
  MAX (day_date)
  FOR day_name IN ([Monday], [Tuesday])
) AS pivotable

赞(0）回复(0）举报 2021-08-09

我来回答

按日期名称转换列中的行

2条答案

相关问题

热门标签

最新问答