SQL语言高级技法演练

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

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

tkey	code	description	year	get_1	get_2	get_3	get_4	get_5	get_6	get_7	get_8	get_9	get_10	get_11	get_12	used_1	used_2	used_3	used_4	used_5	used_6	used_7	used_8	used_9	used_10	used_11	used_12
15	XX1	財務部預算	2004	10000000	10000000	10000000	10000000	10000000	10000000	10000000	10000000	10000000	10000000	10000000	10000000	0	0	0	0	0	0	0	0	0	0	0	0
16	XX2	工程部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000	1200	1300	1400	1000	2000	3000	1000	2000	3000	1002	2002
17	XX3	MIS部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5000	200	2110	1566	200	330	55	222	222	111	33	322
18	XX4	市場部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	0	0	0	0	0	544	0	4522	5412	0	0	45553
19	XX5	動力部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	5422	7521	0	4221	0	4221	4422	4695	5487
20	XX6	發展部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	5422	7521	9442	4221	4522	0	4422	0	5487
21	XX7	計劃部預算	2004	3000000	9000000	9000000	9000000	9000000	9000000	9000000	9000000	9000000	8000000	4000000	3000000	5200	5110	5412	0	7521	0	4221	0	4221	0	4695	5487
22	XX8	生產部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	5422	7521	9442	4221	4522	4221	4422	4695	5487
23	XX9	采購部預算	2004	1000000	1000000	20000000	20000000	9000000	9000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	5422	7521	9442	4221	4522	4221	0	0	0
24	XX10	品質部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	5422	0	0	4221	0	286	8744	522	44
25	XX11	行政部預算	2004	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	1000000	5200	5110	5412	0	7521	9442	4221	0	544	1224	4695	552
33	XX3	MIS預算	2005	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	0	5110	5412	5422	0	0	0	0	0	0	0	0
34	XX1	財務部預算	2005	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	0	0	0	0	0	0	0	0	0	0	0	0
35	XX4	市場部預算	2005	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000	0	0	0	0	0	0	0	0	0	0	0	0
36	XX11	行政部預算	2005	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	100000	0	0	0	120.84	0	0	0	0	0	0	0	0

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

tkey	code	description	年	月	限額	發生額
17	XX3	MIS部預算	2004	1	1000000	2060
17	XX3	MIS部預算	2004	2	1000000	312
17	XX3	MIS部預算	2004	3	1000000	453
17	XX3	MIS部預算	2004	4	1000000	65
17	XX3	MIS部預算	2004	5	1000000	611
17	XX3	MIS部預算	2004	6	1000000	767
17	XX3	MIS部預算	2004	7	1000000	12
17	XX3	MIS部預算	2004	8	1000000	2
17	XX3	MIS部預算	2004	9	1000000	295
17	XX3	MIS部預算	2004	10	1000000	411
17	XX3	MIS部預算	2004	11	1000000	162
17	XX3	MIS部預算	2004	12	1000000	188

        怎幺办？你有办法吗？你能用最简洁的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　于　博客园