SQL语言高级技法演练

博客园 - 留不住的时光

怎样才能将XML文件导入SQL Server 2000？ ERP系统模块完全解析──物料编码分章（二） ERP系统模块完全解析──主生产计划MPS MS Project 2003 安装手记 Asp中一些FSO方面的函数我为Delphi着迷——Danny Thorpe访谈录（转贴）企业的含义 ERP系统模块完全解析──工作中心『数据库系统概论』学习笔记之七 ERP中MRP运算基本名词解析如何使MSSQL的日志文件不再增大？ ERP系统模块完全解析──物料编码分章（一） [旧瓶装新酒]XML在ASP中的应用《发布专题》发布我的WEB ERP预览 ERP系统模块完全解析──BOM分章 ADO.net 中数据库连接方式文档编制步骤『数据库系统概论』学习笔记之六汉字编码知识了解

SQL语言高级技法演练

留不住的时光 · 2005-04-25 · via 博客园 - 留不住的时光

SQL语言高级技法演练
－－再谈列转行二维交叉表的实现

某人在数据库中变态地设计了如下的一个表格，用于记录工厂预算额和实际发生额：

tkeycodedescriptionyearget_1get_2get_3get_4get_5get_6get_7get_8get_9get_10get_11get_12used_1used_2used_3used_4used_5used_6used_7used_8used_9used_10used_11used_1215XX1財務部預算200410000000100000001000000010000000100000001000000010000000100000001000000010000000100000001000000000000000000016XX2工程部預算200410000001000000100000010000001000000100000010000001000000100000010000001000000100000010001200130014001000200030001000200030001002200217XX3MIS部預算2004100000010000001000000100000010000001000000100000010000001000000100000010000001000000500020021101566200330552222221113332218XX4市場部預算200410000001000000100000010000001000000100000010000001000000100000010000001000000100000000000544045225412004555319XX5動力部預算200410000001000000100000010000001000000100000010000001000000100000010000001000000100000052005110541254227521042210422144224695548720XX6發展部預算200410000001000000100000010000001000000100000010000001000000100000010000001000000100000052005110541254227521944242214522044220548721XX7計劃部預算200430000009000000900000090000009000000900000090000009000000900000080000004000000300000052005110541207521042210422104695548722XX8生產部預算200410000001000000100000010000001000000100000010000001000000100000010000001000000100000052005110541254227521944242214522422144224695548723XX9采購部預算20041000000100000020000000200000009000000900000010000001000000100000010000001000000100000052005110541254227521944242214522422100024XX10品質部預算20041000000100000010000001000000100000010000001000000100000010000001000000100000010000005200511054125422004221028687445224425XX11行政部預算2004100000010000001000000100000010000001000000100000010000001000000100000010000001000000520051105412075219442422105441224469555233XX3MIS預算200510000010000010000010000010000010000010000010000010000010000010000010000005110541254220000000034XX1財務部預算200510000100001000010000100001000010000100001000010000100001000000000000000035XX4市場部預算200510000100001000010000100001000010000100001000010000100001000000000000000036XX11行政部預算2005100000100000100000100000100000100000100000100000100000100000100000100000000120.8400000000

但在程序中却又要以如下界面（方式）进行录入和显示：

tkeycodedescription年月限額發生額17XX3MIS部預算200411000000 2060 17XX3MIS部預算20042100000031217XX3MIS部預算20043100000045317XX3MIS部預算2004410000006517XX3MIS部預算20045100000061117XX3MIS部預算20046100000076717XX3MIS部預算2004710000001217XX3MIS部預算200481000000217XX3MIS部預算20049100000029517XX3MIS部預算200410100000041117XX3MIS部預算200411100000016217XX3MIS部預算2004121000000188

怎幺办？你有办法吗？你能用最简洁的SQL语句来实现吗？
如果有兴趣，大家一起来探讨，我这里给出一个示例，可能不是最好的方法，
但也算是比较完美地解决了这道还算比较难办的题。

Create procedure sp_BudGet(
@keyid int
)
As
declare @sql varchar(8000)
set @sql=' select tkey,code,description,year,'
select @sql=@sql+rtrim(name)+' as 限額 ,used'+right(name,len(name)-3)+' as 發生額 from data2 where tkey='+cast(@keyid as varchar(3))+' union all select tkey,code,description,year, ' from syscolumns where id=object_id('data2') and name like 'get_%' order by colorder
set @sql=left(@sql,len(@sql)-len(' union all select tkey,code,description,year,'))
print @sql
exec(@sql) GO

以上存储过程接受一个tkey的传入参数，用于显示某一部门的年度预算，
其中的要点在于在从"syscolumns"表中查询字段名时动态构建一条真实的查询语句，
并且在查询语句中用union合成12个月的预算额和实际发生额。当然，这个表中的字段有
一定的规律，要是全部都没规律，那就只能老老实实的一个一个的写了。

上面的表格貼得不大好，還請見諒，如有更好的語句，請告訴我。：）

上山砍柴去 2005-04-25　于　博客园

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 留不住的时光