橫表轉豎表的範例

-----------------------橫表轉豎表的範例1------------------------------------------
---------臨時表列名查找-----------------------------
SELECT OBJECT_ID('tempdb.qbrclient.#tt')
select * from tempdb.dbo.syscolumns
where ID=OBJECT_ID('tempdb.qbrclient.#tt')
----------------------------------------------
select * from dbo.Forecast_MaterialGroup_flat
where MaterialGroupID in ('4D6404A2-468C-483C-9856-0000A1F63651','8F85B6C3-B2FA-4B37-9C3D-0001062461D5','A8F163E8-F956-46A5-903C-0002F80B6AF4')

declare @Fields table(HorField varchar(50))
insert into @Fields(Horfield)
select distinct name
from syscolumns
where ID=object_ID('dbo.Forecast_MaterialGroup_flat')
  and name<>'MaterialGroupID'
  and name>='2006-01'
  and name<='2006-12'
order by name
--select * from @Fields
declare @tname varchar(50)
select  @tname='##'+replace(newID(),'-','_')
exec('create Table '+@tname+'(MaterialGroupID uniqueidentifier,ForecastMonth varchar(7),ForecastQty bigint)')

declare HorToVer cursor scroll for
select HorField from @Fields
open HorToVer
declare @Field varchar(50)
fetch first from HorToVer into @Field
while @@Fetch_Status=0
begin
    declare @insert varchar(2000)
    select  @insert='insert into '+@tname+'(MaterialGroupID,ForecastMonth,ForecastQty)
    select MaterialGroupID,'''+@Field+''' as ForecastMonth,['+@Field+'] from dbo.Forecast_MaterialGroup_flat
    where MaterialGroupID in (''4D6404A2-468C-483C-9856-0000A1F63651'',''8F85B6C3-B2FA-4B37-9C3D-0001062461D5'',''A8F163E8-F956-46A5-903C-0002F80B6AF4'')'
    print @insert
    exec(@insert)
    fetch next from HorToVer into @Field
end
close HorToVer
deallocate HorToVer
exec('select * from '+@tname+' order by MaterialGroupID,ForecastMonth')
exec('drop table '+@Tname)
--select * from ##7598C1EC_65FE_4B23_AD5C_D2205F50840B
---------------------------橫表轉豎表的範例1------------------------------------------
drop table #mg
create table #mg(MaterialGroupID uniqueidentifier,ForecastMonth varchar(7),ForecastQty int)
declare htov cursor  scroll
for
select name from dbo.syscolumns
where ID=object_ID('dbo.Forecast_MaterialGroup_Flat')
  and name like('2006_%')
open htov
declare @col varchar(50)
fetch first from htov into @col
while @@fetch_status=0
begin
 declare @sql varchar(200)
 select @sql='insert into #mg
select MaterialGroupID,'''+@col+''',['+@col+']
from dbo.Forecast_MaterialGroup_Flat
where ['+@col+']>0'
 print @sql
 exec(@sql)
 fetch next from htov into @col
end
close htov
deallocate htov  
select * from #mg