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

最新推荐文章于 2025-10-13 10:36:53 发布

原创最新推荐文章于 2025-10-13 10:36:53 发布 · 401 阅读

0 ·

CC 4.0 BY-SA版权

T-SQ程序设计同时被 3 个专栏收录

23 篇文章

订阅专栏

T-SQL查询

10 篇文章

订阅专栏

企业级平台管理实践

4 篇文章

订阅专栏

本文介绍了一个SQL过程，用于通过输入表名和模式动态生成视图，实现表中列的数据从宽格式转换为长格式的功能，即列转行操作。此过程适用于需要灵活处理不同表结构的场景。

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 ON
DECLARE @SQLSTR NVARCHAR(500)=N''
DECLARE @SQL NVARCHAR(MAX)=N''

DECLARE MY_CURSOR CURSOR FAST_FORWARD READ_ONLY FOR
WITH 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 SQLSTR
		FROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA
		--ORDER BY C.ORDINAL_POSITION  
		UNION ALL
		SELECT 
		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 SQLSTR
		FROM 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_CURSOR

FETCH FROM MY_CURSOR INTO @SQLSTR

WHILE @@FETCH_STATUS = 0
BEGIN
	/*{ ... CURSOR LOGIC HERE ... }*/
	SET @SQL+=@SQLSTR+' '
	PRINT @SQLSTR
	FETCH FROM MY_CURSOR INTO @SQLSTR
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT '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 ON
IF @TBLNAME IS NOT NULL AND  @WHERESQL IS NOT NULL
BEGIN
	
DECLARE @SQLSTR NVARCHAR(500)=N''
DECLARE @SQL NVARCHAR(MAX)=N''

DECLARE MY_CURSOR CURSOR FAST_FORWARD READ_ONLY FOR
WITH 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 SQLSTR
		FROM INFORMATION_SCHEMA.[COLUMNS] AS C WHERE C.TABLE_NAME=@TBLNAME AND C.TABLE_SCHEMA=@SCHEMA
		--ORDER BY C.ORDINAL_POSITION  
		UNION ALL
		SELECT 
		  
		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 SQLSTR
		FROM 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_CURSOR

FETCH FROM MY_CURSOR INTO @SQLSTR

WHILE @@FETCH_STATUS = 0
BEGIN
	/*{ ... CURSOR LOGIC HERE ... }*/
	SET @SQL+=@SQLSTR+' '
	PRINT @SQLSTR
	FETCH FROM MY_CURSOR INTO @SQLSTR
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
--SELECT 'CREATE VIEW V_AUTO_'+@TBLNAME+'AS '+@SQL AS [SQL]
EXEC(@SQL) 
 
END
ELSE
BEGIN
	PRINT '@TBLNAME IS NULL'
END
 
GO

EXEC UP_AUTO_CREATE_UNPIVOT 't_orderheader','','dbo' ,1