/
dbo.usp_TableUnpivot.sql
108 lines (89 loc) · 5.63 KB
/
dbo.usp_TableUnpivot.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
IF OBJECT_ID('dbo.usp_TableUnpivot', 'P') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_TableUnpivot AS SELECT 1');
go
ALTER PROCEDURE dbo.usp_TableUnpivot (
@databaseName sysname = '',
@schemaName sysname = 'dbo',
@tableName sysname,
@unpivotColumns nvarchar(max) = '',
@constantColumns nvarchar(max) = '',
@excludeColumns nvarchar(max) = '',
@orderBYColumns nvarchar(max) = '',
@debug bit = 0
)
AS
/*
CREATE TABLE dbo.CustomerPhones
(
CustomerID INT PRIMARY KEY, -- FK
Phone1 VARCHAR(32),
PhoneType1 CHAR(4),
Phone2 VARCHAR(32),
PhoneType2 CHAR(4),
Phone3 VARCHAR(32),
PhoneType3 CHAR(4)
);
INSERT INTO dbo.CustomerPhones (CustomerID, Phone1, PhoneType1, Phone2, PhoneType2, Phone3, PhoneType3)
VALUES
(1,'705-491-1111', 'cell', '705-491-1110', 'home', NULL,NULL),
(2,'613-492-2222', 'home', NULL, NULL, NULL, NULL),
(3,'416-493-3333', 'work', '416-493-3330', 'cell', '416-493-3339', 'home');
EXECUTE [usp_TableUnpivot] @databaseName = '',
@schemaName = 'dbo',
@tableName = 'CustomerPhones',
@unpivotColumns = 'CustomerID', /*!!! table columns list for unpivot with comma separator without spaces !!!*/
@excludeColumns = 'TableNameID', /*!!! table columns list for excluding with comma separator without spaces !!!*/
@constantColumns = ', ''Constant1'' AS ConstantColumnName1, ''Constant2'' AS ConstantColumnName2', /*!!! constant values !!!*/
@oderByColumns = '',
@debug = 1; /*!!! only generate sql statement without executing it !!!*/
*/
BEGIN
BEGIN TRY
DECLARE @tableColumns nvarchar(max) = '';
DECLARE @tableNameFull nvarchar(400) = CASE WHEN @databaseName = '' THEN '' ELSE QUOTENAME(@databaseName) + '.' END + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
DECLARE @object_id integer = OBJECT_ID(@tableNameFull);
DECLARE @sqlCommand nvarchar(max) = '';
DECLARE @ParmDefinition nvarchar(500) = '@object_idIN INTEGER, @ColumnsOUT VARCHAR(MAX) OUTPUT';
DECLARE @crlf nvarchar(5) = CHAR(13);
IF @debug = 0 SET NOCOUNT ON ELSE PRINT '/******* Start Debug' + @crlf;
IF @debug = 1 PRINT ISNULL('@tableNameFull = {' + CAST(@tableNameFull AS VARCHAR) + '}', '@tableNameFull = Null');
IF @debug = 1 PRINT ISNULL('@object_id = {' + CAST(@object_id AS VARCHAR) + '}', '@object_id = Null');
SET @sqlCommand = CASE WHEN @databaseName = '' THEN '' ELSE N'USE ' + QUOTENAME(@databaseName) + N';' END + @crlf +
N'SELECT @ColumnsOUT = @ColumnsOUT + QUOTENAME(Name) + '',''' + @crlf +
N'FROM sys.columns sac ' + @crlf +
N'WHERE sac.object_id = @object_idIN' + @crlf +
N' AND Name NOT IN (''' + REPLACE(@unpivotColumns, N',', N''',''') + N''','''
+ REPLACE(@excludeColumns, N',', N''',''') + N''')' + @crlf +
N'ORDER BY Name;';
IF @debug = 1 PRINT ISNULL(N'@sqlCommand = {' + @crlf + @sqlCommand + @crlf + N'}', N'@sqlCommand = Null');
EXECUTE sp_executesql @sqlCommand, @ParmDefinition, @object_idIN = @object_id, @ColumnsOUT = @tableColumns OUTPUT SELECT @tableColumns;
IF @debug = 1 PRINT ISNULL('@tableColumns = {' + @crlf + @tableColumns + @crlf + '}', '@tableColumns = Null');
SET @tableColumns = CASE WHEN LEN(@tableColumns) > 0 THEN LEFT(@tableColumns, LEN(@tableColumns) - 1) END;
SET @sqlCommand = N'SELECT ' + @unpivotColumns + N', val, columns' + @constantColumns + @crlf +
N' FROM ' + @crlf +
N' (' + @crlf +
N' SELECT *' + @crlf +
N' FROM ' + @schemaName + '.' + @tableName + @crlf +
N') s' + @crlf +
N'UNPIVOT' + @crlf +
N' (' + @crlf +
N' val FOR columns IN (' + @tableColumns + ' )' + @crlf +
N' ) p' + @crlf +
CASE WHEN @orderBYColumns != '' THEN N'ORDER BY ' + @orderBYColumns ELSE '' END;
IF @debug = 1 PRINT CAST(ISNULL('@sqlCommand = {' + @crlf + @sqlCommand + @crlf + '}' + @crlf, '@sqlCommand = Null' + @crlf) AS TEXT);
IF @debug = 0 EXECUTE sp_executesql @sqlCommand;
IF @debug = 0 SET NOCOUNT OFF ELSE PRINT 'End Debug *******/';
END TRY
BEGIN CATCH
PRINT(@crlf +
'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE()) +
', User name: ' + CONVERT(sysname, ORIGINAL_LOGIN())
);
PRINT(ERROR_MESSAGE() + @crlf + @crlf);
END CATCH;
END
GO