MSSQL2000列行变换

MSSQL2000列行转换
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 @i=@i+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*/