该方法,实现了,sql server 查询出的结果集,先放入临时表,通过临时表,把列转为行。
--1.把结果放入临时表
SELECT * INTO #Tab FROM dbo.OperateDefine--2.定义返回表变量
DECLARE @retTable TABLE(rowid int,[VALUES] nvarchar(500),code nvarchar(100))
--3.调用转换过程,把jtr
INSERT INTO @retTable
EXEC spmtConvertTmpColumnToRows 'tempdb..#Tab'
--4.相关处理,
SELECT * FROM @retTable
--5.删除临时表
DROP TABLE #Tab
CREATE PROC dbo.spmtConvertTmpColumnToRows( @PassedTableName as NVarchar(255))
AS
begin
--把临时表中的列记录转为行记录
--DECLARE @PassedTableName as NVarchar(255)
--SET @PassedTableName='tempdb..#Tab'
--DECLARE @ActualTableName AS NVarchar(255)
--SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
--FROM INFORMATION_SCHEMA.TABLES
--WHERE TABLE_NAME = @PassedTableName
DECLARE @CreateTableSql NVARCHAR(max)
SET @CreateTableSql=' set nocount on declare @TTT table( rowid int IDENTITY(1,1),'
SELECT @CreateTableSql+=
STUFF((
SELECT ', [' + name + '] nvarchar(500)'
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID(@PassedTableName)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+')'+CHAR(10)--+' insert into @TTT select * from #tab'-- +@PassedTableName+''
SELECT @CreateTableSql+=' insert into @TTT select * from ' +REPLACE(@PassedTableName,'tempdb..','')
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =--''-- 'declare @T table( rowid int IDENTITY(1,1),value varchar(500),code varchar(500))
' select * FROM @TTT
UNPIVOT (
value FOR code IN (
' + STUFF((
SELECT ', [' + name + ']'
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID(@PassedTableName)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + '
)
) unpiv '
-- -- select * from @T
--PRINT @CreateTableSql
--PRINT @SQL
DECLARE @resultSql NVARCHAR(max);
SET @resultSql=@CreateTableSql+@SQL
EXEC (@resultSql)
----DROP TABLE #Tab
end