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