SQL Server - Compare Fields in 2 Tables with PIVOT query

Question

I have the same table in two databases (dbSource and dbTarget) and I'm trying to write a query to compare the field values in each table with a Source/Target diff.

This is the table structure:

CREATE TABLE [dbo].[tblWidget](
    [ID] [int] NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [UpdatedBy] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblWidget] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

For simplicity, I'll say the table in each database has one row:

[dbSource]:

[dbTarget]:

I want the result to look like this:

The closest I could get to the result is a PIVOT query that only returns one of the fields (UpdatedBy). Is there a simple way to include all of the fields in one query vs. doing some kind of UNION with multiple PIVOT statements?

I realize if there is more than one row (example: a row with ID = 2), the expected results won't make sense, so please assume I will only be comparing one row between databases.

This is what I have so far:

SELECT 'UpdatedBy' Field, [0] AS [Source], [1] AS [Target]
FROM
(
SELECT [ID]
      ,[Description]
      ,[UpdatedBy]
      ,1 IsSource
FROM [dbSource].[dbo].[tblWidget]
UNION ALL 
SELECT [ID]
      ,[Description]
      ,[UpdatedBy]
      ,0 IsSource
FROM [dbTarget].[dbo].[tblWidget]
) a
PIVOT (
    MAX(UpdatedBy)
    FOR IsSource IN ([0], [1])
) AS pvt

Thanks

Answer 1

You could join the two tables, and then unpivot the columns to rows:

select s.id, x.*
from dbsource.tblWidget s
inner join dbtarget.tblWidget t on t.id = s.id
cross apply (values 
    ('Description', s.description, t.description),
    ('UpdatedBy',   s.updatedby,   t.updatedby)
) x (field, source, target)

This assumes that column id can be used to relate the two tables - as a consequence, it does not make sense having a row for id in the result (both the source and target values always are the same).