请求协助：在MS SQL Server 2017中实现Excel式透视及特定规则数据转换

针对你提出的两个MS SQL Server 2017相关的问题，我来一步步给你拆解解决方案：

SQL Server自带的PIVOT/UNPIVOT虽然能实现透视，但面对任意数量的列（比如动态的日期列）时不够灵活。用Cross Join配合循环可以实现类似Excel的“任意透视”效果，核心思路是先把宽表转成行式结构，再动态生成透视列，同时用Cross Join补全所有行/列的组合。

具体步骤： #

• 第一步：将宽表Unpivot为行式结构
  先把原始的宽表（比如按日期列存储的客户活动表）转成(client, date_col, value)的行式结构，这样后续更容易处理：

  -- 假设原始表名为ClientActivity
  SELECT client, date_col, value
  INTO #UnpivotedData
  FROM ClientActivity
  UNPIVOT (
      value FOR date_col IN ([1may], [2may], [3may], [4may], [5may]) -- 这里可以后续改成动态获取列名
  ) AS unpvt;
  

• 第二步：用Cross Join补全行/列组合
  先提取所有唯一的日期列，然后用Cross Join生成每个客户与每个日期的笛卡尔积，确保不会因为源数据缺失导致透视后出现NULL：

  SELECT DISTINCT date_col INTO #Dates FROM #UnpivotedData;
  

• 第三步：循环生成动态透视SQL
  用循环遍历所有日期列，拼接出透视需要的列列表，最后执行动态SQL完成透视：

  DECLARE @cols NVARCHAR(MAX) = '';
  DECLARE @current_date NVARCHAR(50);
  DECLARE @cursor CURSOR;
  
  SET @cursor = CURSOR FOR SELECT date_col FROM #Dates ORDER BY date_col;
  OPEN @cursor;
  FETCH NEXT FROM @cursor INTO @current_date;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @cols = @cols + QUOTENAME(@current_date) + ', ';
      FETCH NEXT FROM @cursor INTO @current_date;
  END;
  
  SET @cols = LEFT(@cols, LEN(@cols) - 2);
  
  DECLARE @pivot_sql NVARCHAR(MAX) = N'
  SELECT client, ' + @cols + '
  FROM (
      SELECT ud.client, ud.date_col, ud.value
      FROM #UnpivotedData ud
      CROSS JOIN #Dates d
      WHERE ud.date_col = d.date_col
  ) AS src
  PIVOT (
      MAX(value) FOR date_col IN (' + @cols + ')
  ) AS pvt;';
  
  EXEC sp_executesql @pivot_sql;
  
  -- 清理临时表
  DROP TABLE #UnpivotedData;
  DROP TABLE #Dates;
  

  这里Cross Join的作用是确保每个客户在每个日期都有记录，完美适配Excel透视中“所有行/列都显示”的效果。

针对你提出的转换规则，我建议先把宽表转成行式结构处理（SQL对时间序列的行式数据支持更好），处理完再转回和原始表完全一致的宽表。

规则回顾： #

• 当日值=1：若当日及前一周所有值均为1
• 当日值=0：若当日及前一周所有值均为0
• 否则沿用前一日状态，新用户默认0

具体步骤： #

• 第一步：Unpivot宽表为行式结构
  先把原始宽表转成带实际日期的行式数据：

  -- 假设原始表名为ClientActivityWide
  SELECT 
      client,
      CAST(REPLACE(date_col, 'may', '2024-05-') AS DATE) AS activity_date,
      original_value
  INTO #ActivityLong
  FROM ClientActivityWide
  UNPIVOT (
      original_value FOR date_col IN ([1may], [2may], [3may], [4may], [5may])
  ) AS unpvt;
  

• 第二步：计算转换后的状态
  用窗口函数获取一周内的极值（判断是否全0/全1），再用LAG()函数获取前一日的状态：

  WITH ActivityOrdered AS (
      SELECT 
          client,
          activity_date,
          original_value,
          -- 获取当日及前6天的最小/最大值，判断是否全0或全1
          MIN(original_value) OVER (
              PARTITION BY client 
              ORDER BY activity_date 
              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
          ) AS min_week_value,
          MAX(original_value) OVER (
              PARTITION BY client 
              ORDER BY activity_date 
              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
          ) AS max_week_value,
          -- 获取前一日的转换状态，新用户默认0
          LAG(transformed_value, 1, 0) OVER (
              PARTITION BY client 
              ORDER BY activity_date
          ) AS prev_transformed_value
      FROM #ActivityLong
      -- 先初始化transformed_value为原始值，后续再更新
      CROSS APPLY (SELECT original_value AS transformed_value) AS init
  ),
  TransformedData AS (
      SELECT 
          client,
          activity_date,
          CASE
              WHEN min_week_value = 1 AND max_week_value = 1 THEN 1
              WHEN min_week_value = 0 AND max_week_value = 0 THEN 0
              ELSE prev_transformed_value
          END AS transformed_value
      FROM ActivityOrdered
  )
  SELECT * INTO #TransformedLong FROM TransformedData;
  

  这段逻辑完全对应你的Excel公式：当一周内所有值相同时用当日值，否则沿用前一日状态，新用户第一天默认0。

• 第三步：转回宽表（与原始结构一致）
  用和第一个问题相同的动态透视方法，把行式数据转回宽表：

  -- 获取所有日期，转成原始列名格式（如2024-05-01→1may）
  SELECT DISTINCT 
      CONCAT(DAY(activity_date), 'may') AS date_col
  INTO #TransformedDates
  FROM #TransformedLong;
  
  -- 动态拼接列
  DECLARE @transform_cols NVARCHAR(MAX) = '';
  DECLARE @current_transform_date NVARCHAR(50);
  DECLARE @transform_cursor CURSOR;
  
  SET @transform_cursor = CURSOR FOR SELECT date_col FROM #TransformedDates ORDER BY date_col;
  OPEN @transform_cursor;
  FETCH NEXT FROM @transform_cursor INTO @current_transform_date;
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @transform_cols = @transform_cols + QUOTENAME(@current_transform_date) + ', ';
      FETCH NEXT FROM @transform_cursor INTO @current_transform_date;
  END;
  
  SET @transform_cols = LEFT(@transform_cols, LEN(@transform_cols) - 2);
  
  -- 生成并执行透视SQL
  DECLARE @transform_pivot_sql NVARCHAR(MAX) = N'
  SELECT client, ' + @transform_cols + '
  FROM (
      SELECT 
          client,
          CONCAT(DAY(activity_date), ''may'') AS date_col,
          transformed_value
      FROM #TransformedLong
      CROSS JOIN #TransformedDates d
      WHERE CONCAT(DAY(activity_date), ''may'') = d.date_col
  ) AS src
  PIVOT (
      MAX(transformed_value) FOR date_col IN (' + @transform_cols + ')
  ) AS pvt;';
  
  EXEC sp_executesql @transform_pivot_sql;
  
  -- 清理临时表
  DROP TABLE #ActivityLong;
  DROP TABLE #TransformedLong;
  DROP TABLE #TransformedDates;
  

内容的提问来源于stack exchange，提问作者Semyon-coder