Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save shaneis/04269317094a0a3ca45dd810b2cfb03f to your computer and use it in GitHub Desktop.
Save shaneis/04269317094a0a3ca45dd810b2cfb03f to your computer and use it in GitHub Desktop.
Pivot Method Table Column Differences Test
SET STATISTICS IO, TIME ON;
GO
SELECT Pivot1.ColumnName,
Pivot1.[dbo.TableColumnDifference01],
Pivot1.[dbo.TableColumnDifference02],
CASE WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] = 1
THEN 'Both'
WHEN [dbo.TableColumnDifference01] = 1 AND [dbo.TableColumnDifference02] IS NULL
THEN 'Table 1 only'
WHEN [dbo.TableColumnDifference01] IS NULL AND [dbo.TableColumnDifference02] = 1
THEN 'Table 2 only'
ELSE 'Eh...this should not really happen'
END AS HumanReadableFormat
FROM ( SELECT
c.[name] AS ColumnName,
tb.TableName,
1 AS ColumnExists
FROM sys.columns AS c
RIGHT JOIN ( VALUES
(OBJECT_ID(N'dbo.TableColumnDifference01', N'U'), 'dbo.TableColumnDifference01'),
(OBJECT_ID(N'dbo.TableColumnDifference02', N'U'), 'dbo.TableColumnDifference02')
) AS tb (ObjectID, TableName)
ON c.object_id = tb.ObjectID
) AS UnPivotedColumns
PIVOT (
MAX(ColumnExists) FOR TableName IN ([dbo.TableColumnDifference01], [dbo.TableColumnDifference02])
) AS Pivot1
ORDER BY Pivot1.ColumnName ASC;
GO
SET STATISTICS IO, TIME OFF;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment