SQL Server 2008 列转行实例

最新推荐文章于 2024-07-23 15:38:58 发布

weixin_33809981

最新推荐文章于 2024-07-23 15:38:58 发布

阅读量308

点赞数

文章标签：数据库

原始表中的数据如下所示：

需求得到如下结果：

SQL如下：

SELECT prodline, 
       week, 
       weekday, 
       weekdaysip, 
       Dateadd(dd, CONVERT(INT, RIGHT(week, 2)) * 7 - Datepart(dw, Cast(CONVERT( 
                                                                   INT, 
                                                                   LEFT(week, 4) 
                                                                   ) 
        - 1 AS 
        VARCHAR(4)) 
        + '-12-31') + CASE 
        weekday 
        WHEN 'Mon' THEN 1 
        WHEN 'Tu' THEN 2 
        WHEN 'Wed' THEN 3 
        WHEN 'Thu' THEN 4 
        WHEN 'Fri' THEN 5 
        ELSE 0 
          END, 
        Cast(CONVERT(INT, LEFT(week, 4)) AS VARCHAR(4)) 
        + 
        '-01-01') AS WeekdayDate 
FROM   apps.target_sip 
       UNPIVOT(weekdaysip 
              FOR weekday IN (mon, 
                              tu, 
                              wed, 
                              thu, 
                              fri)) P