如何用SQL查询透视不规范表头的复杂预算数据表

楼主 whsfhwm	Q:如何用SQL查询透视不规范表头的复杂预算数据表？ A:数据源工作簿见附件，要求做成透视表，页字段要有业务员和月份可供选择，数据区域要有销售额及毛利、销售预算、实际销售；毛利预算、实际毛利等。具体做法： 1 利用EXCEL-SQL(OLE DB)，放弃了E:H列（2010年度预算）的数据，因为可以根据将所有月份汇总来统计。 2 将工作表[sheet1$a4:dl]区域作为数据源，也就是第1到3行表头部分放弃，然后在SQL语句中手工命名列字段。 3 配合上面第2条，需要将“编辑OLE DB查询”对话框中连接编辑框里的HDR=YES部分修改为HDR=NO,意思是数据源中不包含标题行。这一点不常用，所以要注意哈。 4 采用union all联合查询方式将数据源重新构建，以便能够按月份或业务员进行分页透视。最后的SQL语句为： select F1 AS 客户代码,F2 AS 客户名称,F3 AS 业务员,F4 AS 客户类别,F9 AS 预计销售额,F10 AS 实际销售额,F11 AS 销售完成率,F12 AS [预计考核毛利额（含税）],F13 AS 实际考核毛利额（含税）,F14 AS 毛利完成率,F15 AS 预计回款额,F16 AS 实际回款额,F17 AS 回款完成率,"1月" as 月份 from [sheet1$a4:dl] union all select F1,F2,F3,F4,F18,F19,F20,F21,F22,F23,F24,F25,F26,"2月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F27,F28,F29,F30,F31,F32,F33,F34,F35,"3月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F36,F37,F38,F39,F40,F41,F42,F43,F44,"4月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F45,F46,F47,F48,F49,F50,F51,F52,F53,"5月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F54,F55,F56,F57,F58,F59,F60,F61,F62,"6月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F63,F64,F65,F66,F67,F68,F69,F70,F71,"7月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F72,F73,F74,F75,F76,F77,F78,F79,F80,"8月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F81,F82,F83,F84,F85,F86,F87,F88,F89,"9月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F90,F91,F92,F93,F94,F95,F96,F97,F98,"10月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F99,F100,F101,F102,F103,F104,F105,F106,F107,"11月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F108,F109,F110,F111,F112,F113,F114,F115,F116,"12月" from [sheet1$a4:dl] 如何用SQL查询透视不规范表头的复杂预算数据表.rar
2楼 mn860429	谢谢，学习了
3楼海洋之星	学习了，谢谢了
4楼 xinger7	这个太难了.绕道 ......
5楼 Ex_Study	请问这里面select F1 AS 客户代码,F2 AS 客户名称,F3 AS 业务员,F4 AS 客户类别,F9 AS 预计销售额,F10 AS 实际销售额,F11 AS 销售完成率,F12 AS [预计考核毛利额（含税）],F13 AS 实际考核毛利额（含税）,F14 AS 毛利完成率,F15 AS 预计回款额,F16 AS 实际回款额,F17 AS 回款完成率,"1月" as 月份 from [sheet1$a4:dl] union all select F1,F2,F3,F4,F18,F19,F20,F21,F22,F23,F24,F25,F26,"2月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F27,F28,F29,F30,F31,F32,F33,F34,F35,"3月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F36,F37,F38,F39,F40,F41,F42,F43,F44,"4月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F45,F46,F47,F48,F49,F50,F51,F52,F53,"5月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F54,F55,F56,F57,F58,F59,F60,F61,F62,"6月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F63,F64,F65,F66,F67,F68,F69,F70,F71,"7月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F72,F73,F74,F75,F76,F77,F78,F79,F80,"8月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F81,F82,F83,F84,F85,F86,F87,F88,F89,"9月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F90,F91,F92,F93,F94,F95,F96,F97,F98,"10月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F99,F100,F101,F102,F103,F104,F105,F106,F107,"11月" from [sheet1$a4:dl] union all select F1,F2,F3,F4,F108,F109,F110,F111,F112,F113,F114,F115,F116,"12月" from [sheet1$a4:dl] 为什么是 F1,F2,F3,F4,F9，F10,F11 可以是E1，E2,E3,E4,E9,E10,E11 吗
6楼海洋之星	可以啊
7楼 Ex_Study	我这样写的： select E1 as 客户代码,E2 as 客户名称,E3 as 业务员,E4 as 客户类别,E9 as 预计销售额,E10 as 实际销售额,E11 as 销售完成率,E12 as [预计考核毛利额(含税)] from [Sheet1$a4:dl33] 运行之后弹出下图当改为： select F1 as 客户代码,F2 as 客户名称,F3 as 业务员,F4 as 客户类别,F9 as 预计销售额,F10 as 实际销售额,F11 as 销售完成率,F12 as [预计考核毛利额(含税)] from [Sheet1$a4:dl33] 就没有问题 QQ图片20140108102719.jpg