自动生成unpivot,方便table展示字符度量

DECLARE @TBLNAME NVARCHAR(100),@SCHEMA NVARCHAR(100) --SELECT @TBLNAME='T_ORGAN_OUTLET_WEEKLY_REPORT',@SCHEMA='DBO'CREATE PROC up_auto_create_unpivot( @TBLNAME NVARCHAR(100),@SCHEMA NVARCHAR(100))as--SELECT @TBLNAME='V_RELATION2',@SCHEMA='DBO'SET NOCOUNT ONDECLARE @SQLSTR NVARCHAR(500)=N''DECLARE @SQL NVARCHAR(MAX)=N''DECLARE MY_CURSOR CURSOR FAST_FORWARD READ_ONLY FORWITH GENSQL AS(SELECT C.ORDINAL_POSITION,CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION)=1 THEN ' WITH TEMP AS(SELECT TOP 11 ROW_NUMBER()OVER(ORDER BY '+C.COLUMN_NAME+')ID ,' ELSE ',' END +CASE WHEN DATA_TYPE LIKE 'DATE%' THEN ' CAST( CONVERT(NVARCHAR(10),'+C.COLUMN_NAME+',120) AS NVARCHAR(50))' ELSE ' CAST('+C.COLUMN_NAME+' AS NVARCHAR(50))' END +'AS '+C.COLUMN_NAME+''+ CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION DESC)=1 THEN ' FROM  '+C.TABLE_SCHEMA+'.'+C.TABLE_NAME +')'ELSE ' ' END SQLSTRFROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA--ORDER BY C.ORDINAL_POSITION  UNION ALLSELECT C.ORDINAL_POSITION+1000 ORDINAL_POSITION,CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION)=1 THEN 'SELECT * FROM  TEMP UNPIVOT ( VAL FOR KPI IN ('+C.COLUMN_NAME  ELSE ','+C.COLUMN_NAME END + CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION DESC)=1 THEN ') )UPVT INNER JOIN TEMP T1 ON T1.ID=UPVT.ID' ELSE ' ' END SQLSTRFROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA--ORDER BY C.ORDINAL_POSITION     )SELECT SQLSTR FROM GENSQL ORDER BY  ORDINAL_POSITION OPEN MY_CURSORFETCH FROM MY_CURSOR INTO @SQLSTRWHILE @@FETCH_STATUS = 0BEGIN/*{ ... CURSOR LOGIC HERE ... }*/SET @SQL+=@SQLSTR+' 'PRINT @SQLSTRFETCH FROM MY_CURSOR INTO @SQLSTRENDCLOSE MY_CURSORDEALLOCATE MY_CURSORSELECT 'CREATE VIEW V_AUTO_'+@TBLNAME+'AS '+@SQL AS [SQL]--EXEC(@SQL)     --SELECT * FROM INFORMATION_SCHEMA.[COLUMNS] AS C 

ALTER PROC UP_AUTO_CREATE_UNPIVOT( @TBLNAME NVARCHAR(100)=NULL,@WHERESQL NVARCHAR(500)='',@SCHEMA NVARCHAR(10)='DBO',@TOP INT=10)AS--SELECT @TBLNAME='V_RELATION2',@SCHEMA='DBO'SET NOCOUNT ONIF @TBLNAME IS NOT NULL AND  @WHERESQL IS NOT NULLBEGINDECLARE @SQLSTR NVARCHAR(500)=N''DECLARE @SQL NVARCHAR(MAX)=N''DECLARE MY_CURSOR CURSOR FAST_FORWARD READ_ONLY FORWITH GENSQL AS(SELECT C.ORDINAL_POSITION,CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION)=1 THEN ' WITH TEMP AS(SELECT  '+CASE WHEN @top >=0 THEN ' top '+cast( @TOP AS NVARCHAR(10)) ELSE '' END +' ROW_NUMBER()OVER(ORDER BY '+C.COLUMN_NAME+')[ID#] ,' ELSE ',' END +CASE WHEN DATA_TYPE LIKE 'DATE%' THEN ' CAST( CONVERT(NVARCHAR(10),'+C.COLUMN_NAME+',120) AS NVARCHAR(50))' ELSE ' CAST('+C.COLUMN_NAME+' AS NVARCHAR(50))' END +'AS '+C.COLUMN_NAME+''+ CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION DESC)=1 THEN ' FROM  '+C.TABLE_SCHEMA+'.'+C.TABLE_NAME +' '+@WHERESQL+')'ELSE ' ' END SQLSTRFROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA--ORDER BY C.ORDINAL_POSITION  UNION ALLSELECT   C.ORDINAL_POSITION+1000 ORDINAL_POSITION,CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION)=1 THEN 'SELECT '+QUOTENAME(@TBLNAME,'''' )+' TBLNAME,'+QUOTENAME(@SCHEMA,'''' )+' TABLE_SCHEMA, UPVT.[ID#] [IDD#],ORDINAL_POSITION,KPI [KPI#],VAL [VAL#],T1.* FROM  TEMP UNPIVOT ( VAL FOR KPI IN ('+C.COLUMN_NAME  ELSE ','+C.COLUMN_NAME END + CASE WHEN ROW_NUMBER()OVER(ORDER BY C.ORDINAL_POSITION DESC)=1 THEN ') )UPVT INNER JOIN TEMP T1 ON T1.[ID#]=UPVT.[ID#]'+' INNER JOIN INFORMATION_SCHEMA.[COLUMNS] AS C ON C.TABLE_NAME='+QUOTENAME(@TBLNAME,'''' )+' AND C.TABLE_SCHEMA='+QUOTENAME(@SCHEMA,'''' )+' AND C.COLUMN_NAME=KPI ' ELSE ' ' END SQLSTRFROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA--ORDER BY C.ORDINAL_POSITION     )SELECT SQLSTR FROM GENSQL ORDER BY  ORDINAL_POSITION OPEN MY_CURSORFETCH FROM MY_CURSOR INTO @SQLSTRWHILE @@FETCH_STATUS = 0BEGIN/*{ ... CURSOR LOGIC HERE ... }*/SET @SQL+=@SQLSTR+' 'PRINT @SQLSTRFETCH FROM MY_CURSOR INTO @SQLSTRENDCLOSE MY_CURSORDEALLOCATE MY_CURSOR--SELECT 'CREATE VIEW V_AUTO_'+@TBLNAME+'AS '+@SQL AS [SQL]EXEC(@SQL)  ENDELSEBEGINPRINT '@TBLNAME IS NULL'END GOEXEC UP_AUTO_CREATE_UNPIVOT 't_orderheader','','dbo' ,1