27,579
社区成员
发帖
与我相关
我的任务
分享
--关键词 COALESCE
------语句
DECLARE @aa NVARCHAR(1000)
SELECT @aa=COALESCE(@aa,'')+''''+s.name+''','+'CAST('+s.name+' AS NVARCHAR(50)) FROM TB_ChangePeople union select TOP 1 '
FROM (SELECT TOP 1 * FROM dbo.TB_ChangePeople ) t
LEFT JOIN syscolumns s ON 1=1
Where s.ID=OBJECT_ID('TB_ChangePeople')
DECLARE @sql NVARCHAR(3000)
SET @sql='insert into tableB (columns1,columns2) select top 1 '+@aa+' TB_ChangePeople '
PRINT @sql
------结果
insert into tableB (columns1,columns2)
select top 1 'id',CAST(id AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 't_Code',CAST(t_Code AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldDepartMentID',CAST(c_OldDepartMentID AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldDepartMentName',CAST(c_OldDepartMentName AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldExecuteCard',CAST(c_OldExecuteCard AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldExecuteName',CAST(c_OldExecuteName AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldContent',CAST(c_OldContent AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldBeginTime',CAST(c_OldBeginTime AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_OldEndTime',CAST(c_OldEndTime AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_NowDepartMentID',CAST(c_NowDepartMentID AS NVARCHAR(50)) FROM TB_ChangePeople
union select TOP 1 'c_NowDepartMentName',CAST(c_NowDepartMentName AS NVARCHAR(50)) FROM TB_ChangePeople
;WITH t(C1,C2,C3,A1) AS (
SELECT '12','13','3A','a' UNION ALL
SELECT '121','131','3A1','aa'
)
SELECT vt.col1,vt.col2,vt.A1 FROM t
UNPIVOT (COL2 FOR COL1 IN (c1,c2,c3)) AS vt
/*
col1 col2 A1
C1 12 a
C2 13 a
C3 3A a
C1 121 aa
C2 131 aa
C3 3A1 aa
*/
;WITH t(C1,C2,C3) AS (
SELECT '12','13','3A'
)
SELECT * FROM t
UNPIVOT (COL FOR c IN (c1,c2,c3)) AS vt
/*
COL c
12 C1
13 C2
3A C3
*/