USE MASTER
GO
IF OBJECT_ID('dbo.sp_pivot') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_pivot AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_pivot
@TableName SYSNAME = NULL,
@ColumnValues VARCHAR(MAX) = NULL,
@ColumnsRetrieved VARCHAR(MAX) = NULL,
@SQL VARCHAR(MAX) = '' OUTPUT,
@PrintSQL BIT = 1,
@ExecSQL BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @TableName = LTRIM(RTRIM(@TableName))
IF NOT (ISNULL(@TableName, '') <> ''
AND ISNULL(@ColumnValues, '') <> ''
AND ISNULL(@ColumnsRetrieved, '') <> '')
BEGIN
PRINT 'EXEC dbo.sp_pivot
@TableName = ''tTax.BuildingEquipment'',
@ColumnValues = ''BldgSeqNbr=1,BldgSeqNbr=2,EqpmtSeq=1,EqpmtSeq=2'',
@ColumnsRetrieved = ''EqpmtCd,EqpmtCd2''
'
RETURN
END
DECLARE @ColTable TABLE (NAME SYSNAME,
Value VARCHAR(500) PRIMARY KEY CLUSTERED (NAME, VALUE))
DECLARE @ColumnList TABLE (TableName SYSNAME NOT NULL,
ColumnName SYSNAME NULL,
ColumnType SYSNAME NOT NULL,
key_ordinal TINYINT NULL,
column_id INT NOT NULL) ;
INSERT @ColTable
SELECT DISTINCT
c.Name,
c.VALUE
FROM Util.dbo.ParseDelimitedTableColumns32(@ColumnValues, '=', ',')
CROSS APPLY (SELECT LTRIM (RTRIM (C1)) AS Name, LTRIM (RTRIM (C2)) AS VALUE) c
INSERT @ColumnList
SELECT CAST(s.name + '.' + t.name + '' AS VARCHAR(256)) AS TableName,
c.name AS ColumnName,
ty.name AS ColumnType,
ic.key_ordinal,
c.column_id
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
INNER JOIN sys.types ty (NOLOCK) ON c.user_type_id = ty.user_type_id
OUTER APPLY (SELECT IC.key_ordinal
FROM sys.indexes AS ix (NOLOCK)
INNER JOIN sys.index_columns AS ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
INNER JOIN sys.columns AS cl (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ix.is_primary_key = 1
AND cl.name = c.name
AND t.OBJECT_ID = ix.OBJECT_ID) ic
WHERE t.OBJECT_ID = OBJECT_ID(@TableName)
ORDER BY s.name,
t.name,
ISNULL(ic.key_ordinal, 255),
c.column_id ;
WITH step1
AS (SELECT DISTINCT
a.ColumnName,
a.ColumnType,
q.quot,
b.Value,
DENSE_RANK() OVER (ORDER BY a.KEY_ORDINAL) AS NameRank
FROM @ColumnList a
INNER JOIN @ColTable B ON a.ColumnName = b.Name
CROSS APPLY (SELECT CASE WHEN ColumnType LIKE '%CHAR%'
OR ColumnType LIKE '%date%'
OR ColumnType LIKE '%time%' THEN ''''
ELSE ''
END AS quot) q
WHERE KEY_ORDINAL IS NOT NULL),
step2
AS (SELECT b.ColumnName
FROM Util.dbo.ParseDelimited(@ColumnsRetrieved, ',') a
INNER JOIN @ColumnList b ON LTRIM(RTRIM(a.Field)) = b.ColumnName
WHERE b.key_ordinal IS NULL),
step3
AS (SELECT k.*
FROM (SELECT * FROM step1 WHERE NameRank = 1) a
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 2) b ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 3) c ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 4) d ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 5) e ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 6) f ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 7) g ON 1 = 1
LEFT OUTER JOIN (SELECT * FROM step1 WHERE NameRank = 8) h ON 1 = 1
CROSS APPLY (SELECT 'CASE WHEN ' + Util.dbo.StringConcat(ColumnName + ' = ' + quot + value + quot,
' AND ') AS caselogic,
'_' + Util.dbo.StringConcat(value, '_') AS columnalias
FROM (SELECT a.ColumnName,
a.ColumnType,
a.quot,
a.VALUE
WHERE a.columnname IS NOT NULL
UNION ALL
SELECT b.ColumnName,
b.ColumnType,
b.quot,
b.VALUE
WHERE b.columnname IS NOT NULL
UNION ALL
SELECT c.ColumnName,
c.ColumnType,
c.quot,
c.VALUE
WHERE c.columnname IS NOT NULL
UNION ALL
SELECT d.ColumnName,
d.ColumnType,
d.quot,
d.VALUE
WHERE d.columnname IS NOT NULL
UNION ALL
SELECT e.ColumnName,
e.ColumnType,
e.quot,
e.VALUE
WHERE e.columnname IS NOT NULL
UNION ALL
SELECT f.ColumnName,
f.ColumnType,
f.quot,
f.VALUE
WHERE f.columnname IS NOT NULL
UNION ALL
SELECT g.ColumnName,
g.ColumnType,
g.quot,
g.VALUE
WHERE g.columnname IS NOT NULL
UNION ALL
SELECT h.ColumnName,
h.ColumnType,
h.quot,
h.VALUE
WHERE h.columnname IS NOT NULL) k) k),
STEP4
AS (SELECT TOP 999999
TableName,
ColumnName
FROM @ColumnList a
WHERE KEY_ORDINAL IS NOT NULL
AND NOT EXISTS ( SELECT *
FROM @ColTable b
WHERE a.ColumnName = b.Name )
ORDER BY key_ordinal),
step6
AS (SELECT 'WHERE ' + Util.dbo.StringConcat(SQL, '
AND ') AS SQL
FROM (SELECT ColumnName + ' IN (' + Util.dbo.StringConcat(quot + VALUE + quot, ', ') + ')' AS SQL
FROM step1
GROUP BY ColumnName) b)
SELECT @SQL = 'SELECT
' + ColList + ',
' + Util.dbo.StringConcat('MAX(' + a.caselogic + ' THEN ' + b.ColumnName + ' END) AS [' + b.ColumnName
+ COLUMNALIAS + ']', ',
') + '
FROM ' + TableName + '
' + ISNULL((SELECT SQL FROM STEP6), '') + '
GROUP BY ' + ColList
FROM step3 a
CROSS JOIN step2 b
CROSS APPLY (SELECT TableName,
Util.dbo.StringConcat(ColumnName, ', ') AS ColList
FROM STEP4
GROUP BY TableName) c
GROUP BY ColList,
TableName
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject
sp_pivot
GO