MSSQL2000列行转换
表:traderpric供应单价表
供应商, 物料,(月份单价) 1月,2月,3月,4月,5月,6月,7月,8月,9月,10月,11月,12月
traderid,materialid,zd1,zd2,zd3,zd4,zd5,zd6,zd7,zd8,zd9,zd10,zd11,zd12
1 , 3 , 1 ,1.5, 3 , 4 ,4.3,6, 7 ,8 ,9 ,10 ,11 ,12
2 ---以下省略
3
要求结果：转换成视图结果
供应商，物料， 单价 ,月份
traderid,materialid,price , month
1 , 3 , 1 , 1
1 , 3 , 1.5 , 2
1 , 3 , 3 , 3
1 , 3 , 4 , 4
1 , 3 , 4.3 , 5
1 , 3 , 6 , 6
1 , 3 , 7 , 7
1 , 3 , 8 , 8
1 , 3 , 9 , 9
1 , 3 , 10 , 10
1 , 3 , 11 , 11
1 , 3 , 12 , 12
------解决方案--------------------
- SQL code
select traderid,materialid,zd1 as price,1 as [month] from traderpricunion allselect traderid,materialid,zd2,2 from traderpricunion allselect traderid,materialid,zd3,3 from traderpricunion allselect traderid,materialid,zd4,4 from traderpricunion allselect traderid,materialid,zd5,5 from traderpricunion allselect traderid,materialid,zd6,6 from traderpricunion allselect traderid,materialid,zd7,7 from traderpricunion allselect traderid,materialid,zd8,8 from traderpricunion allselect traderid,materialid,zd9,9 from traderpricunion allselect traderid,materialid,zd10,10 from traderpricunion allselect traderid,materialid,zd11,11 from traderpricunion allselect traderid,materialid,zd12,12 from traderpric
------解决方案--------------------
- SQL code
if object_id('traderprice') is not null drop table traderpricegocreate table traderprice(traderid int,materialid int,zd1 money,zd2 money,zd3 money,zd4 money,zd5 money,zd6 money,zd7 money,zd8 money,zd9 money,zd10 money,zd11 money,zd12 money)goinsert into traderpriceselect 1 , 3 , 1 ,1.5, 3 , 4 ,4.3,6, 7 ,8 ,9 ,10 ,11 ,12godeclare @sql varchar(4000),@i intset @i=1while @i<=12begin set @sql=isnull(@sql+' union all ','')+'select traderid,materialid,zd'+cast(@i as varchar(2))+' as price,'+cast(@i as varchar(2))+' as [month] from traderprice' set @[email protected]+1endexec(@sql)/*traderid materialid price month----------- ----------- --------------------- -----------1 3 1.00 11 3 1.50 21 3 3.00 31 3 4.00 41 3 4.30 51 3 6.00 61 3 7.00 71 3 8.00 81 3 9.00 91 3 10.00 101 3 11.00 111 3 12.00 12*/