行转列SQL语句

转载于 2016-08-17 11:04:00 发布 · 71 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sulong/p/5779141.html

本文介绍了一种使用SQL查询特定月份经理工作计划的方法，通过复杂的UNPIVOT操作将表中的列转换为行，以便进行更灵活的数据筛选。此查询适用于固定年份及月份的工作调度表，能够返回指定经理在该时间段内的工作安排。

select top 10 * from schedule_month where years=2016 and months=7 and managerid=45
SELECT top 10 cast(years AS varchar) + '-' + RIGHT('0' + CAST(months AS varchar), 2) + '-' + RIGHT('0' + REPLACE(days,
'day', ''), 2) AS trueday, job_title_id, managerid, years, months, days, dayvalue
FROM (SELECT job_title_id, managerid, years, months, day1, day2, day3, day4, day5, day6, day7, day8, day9,
day10, day11, day12, day13, day14, day15, day16, day17, day18, day19, day20, day21, day22,
day23, day24, day25, day26, day27, day28, day29, day30, day31
FROM schedule_month) p UNPIVOT (dayvalue FOR days IN (day1, day2, day3, day4, day5, day6, day7,
day8, day9, day10, day11, day12, day13, day14, day15, day16, day17, day18, day19, day20, day21, day22,
day23, day24, day25, day26, day27, day28, day29, day30, day31))
AS unpvt/*years=@years and months=@months*/