1.unpivot
with T(LOTNO, IDName, DepartmentCode, OperateType, State, ModTime, C1_001_001, C1_001_002, [Value], CreateTime, GetTime, FinishTime, Status)
as
(
select lower(newid()),IDName,@DepartmentCode,0,0,getdate(),C1_001_001,C1_001_002,[Value],getdate(),null,null,'00' from
(
select JC_CYXX.SFZHM as C1_001_001 , JC_CYXX.CYXM as C1_001_002 ,
convert(nvarchar(max),JC_CYXX.SFZHM) as SFZHM,convert(nvarchar(max),JC_CBXX.CBBZ) as CBBZ,
convert(nvarchar(max),JC_CBXX.YXBZ) as YXBZ,convert(nvarchar(max),JC_JTXX.XZDM) as XZDM,
convert(nvarchar(max),JC_JTXX.XZCDM) as XZCDM,convert(nvarchar(max),JC_JTXX.JCDM) as JCDM
from JC_CBXX
left join JC_CYXX on JC_CBXX.HZYLHM = JC_CYXX.HZYLHM
left join JC_JTXX on JC_JTXX.JCDM = JC_JTXX.JCDM and JC_JTXX.JTHM = JC_CYXX.JTHM and JC_CYXX.JTHM = JC_JTXX.JTHM
where 1!=2
) p
unpivot
(
[Value] for IDName in (SFZHM,CBBZ,YXBZ,XZDM,XZCDM,JCDM)) as unpt
)
insert into TempTable select * from T
Notes
--1.selected column can not appeared in the unpivot col list,if you want to, please use alias
--2.all of the column specified in the unpivot list must have same datatype and same data size
2.SQL 2005 left/right join 问题
原来2000里有时图方便这样写left/right join
select a.a,b.b from tableA a,tableB b where a.a*=b.b
但是在2005里这样写就不对了，
select a.a,b.b from tableA a left join tableB b on a.a=b.b
如果有2000的库升级到2005，又不想修改sql的话,如果没有用到2005的新特性的话，可以这样修改
EXEC sp_dbcmptlevel <DBName>, 80;
GO
3.查看临时表存在
if object_id('tempdb..#TC_TableRelation') is null
begin
CREATE TABLE [#TC_TableRelation](
[ID] [nvarchar](255) NOT NULL,
[ViewName] [nvarchar](50) NOT NULL,
[MasterTableName] [nvarchar](255) NULL,
[PrimaryKey] [nvarchar](50) NOT NULL,
[PrimaryValue] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL
) ON [PRIMARY]
end
或者
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#TC_TableRelation') and type='U')
drop table #TC_TableRelation
4.存储过程是不支持Table类型参数的,解决办法
a).用游标变量代替
b).使用2005里的xml数据类型
Ref:Passing a Table to A Stored Procedure