I've got about 15 tables I need to check for records that don't match, based on an ID. So, for example, I've got one table with contact info:
TransID FirstName LastName Phone Extn
80071 Michael Kriegel 888-555-6265 1111
80071 Pam Morrow 888-555-9999 2222
80071 Barbara Smith 888-555-7770 3333
80071 Yessika Zurita 888-555-6622 4444
80072 Pam Morrow 888-555-9999 2222
80072 Barbara Smith 888-555-7777 3333
80072 Yessika Zurita 888-555-9954 4444
80072 Michael Kriegel 888-555-6265 1122
We've got Michael Kriegel whose Extn is different between the two TransIDs, and we have Barbara Smith and Zurita Yesskia whose phone numbers are different.
What I need to do is figure out a way to show which records are different, and show which field(s) is/are different. But I also need to write this so it's generic, because I don't want to hard-code 15 different comparisons.
I'd like to get my output to look like this:
Name Field TransID_80071 TransID_80072
Michael Kriegel Extn 1111 1122
Barbara Smith Phone 888-555-7770 888-555-7777
Zurita Yessika Phone 888-555-6622 888-555-9954
I'm messing around with UNPIVOTs, but I can't get there. I've got this code:
SELECT TransID , Value , FieldName
FROM
(SELECT Max(TransID) as TransID, Convert(VarChar(250),FirstName) as FirstName,
Convert(VarChar(250),LastName) as LastName,
Convert(VarChar(250),Phone) as Phone, Convert(VarChar(250),CoExtn) as CoExtn
FROM tblSQLContacts
WHERE TransID IN ('80071', '80072')
GROUP BY FirstName, LastName, Phone, CoExtn
HAVING count(*) < 2
) p
UNPIVOT
(FieldName FOR Value IN
(FirstName, LastName, Phone, CoExtn)
)AS unpvt
This gives me a result of:
TransID Value FieldName
80071 FirstName Barbara
80071 LastName Smith
80071 Phone 888-555-7770
80071 Extn 3333
80072 FirstName Barbara
80072 LastName Smith
80072 Phone 888-555-7777
80072 Extn 3333
80071 FirstName Michael
80071 LastName Kriegel
80071 Phone 888-555-6265
80071 Extn 1111
80072 FirstName Michael
80072 LastName Kriegel
80072 Phone 888-555-6265
80072 Extn 1122
80071 FirstName Yessika
80071 LastName Zurita
80071 Phone 888-555-6622
80071 Extn 4444
80072 FirstName Yessika
80072 LastName Zurita
80072 Phone 888-555-9954
80072 Extn 4444
So, I'm pulling the right records, but I can't figure out how to get it into the format I want it in. Can anyone get me over the finish line?
if you have data like this..
CREATE TABLE #tSQLContacts
([TransID] int, [FirstName] varchar(250), [LastName] varchar(250), [Phone] varchar(250), [Extn] varchar(250))
;
INSERT INTO #tSQLContacts
([TransID], [FirstName], [LastName], [Phone], [Extn])
VALUES
(80071, 'Michael', 'Kriegel', '888-555-6265', '1111'),
(80071, 'Pam', 'Morrow', '888-555-9999', '2222'),
(80071, 'Barbara', 'Smith', '888-555-7770', '3333'),
(80071, 'Yessika', 'Zurita', '888-555-6622', '4444'),
(80072, 'Pam', 'Morrow', '888-555-9999', '2222'),
(80072, 'Barbara', 'Smith', '888-555-7777', '3333'),
(80072, 'Yessika', 'Zurita', '888-555-9954', '4444'),
(80072, 'Michael', 'Kriegel', '888-555-6265', '1122')
;
you can get your desired result like this.
;WITH cte AS (
SELECT *,
DENSE_RANK() OVER (PARTITION BY Rnk1, ColumnName ORDER BY ColumnValue) Rnk2
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY [FirstName], [LastName]) Rnk1
FROM #tSQLContacts
WHERE TransID IN ('80071', '80072')
) t
UNPIVOT
(
ColumnValue
FOR ColumnName IN ([Phone], [Extn] )
) up
)
SELECT CONCAT(FirstName, ' ',LastName) AS NAME,
ColumnName AS Field,
[80071] AS [TransID_80071],
[80072] AS [TransID_80072]
FROM (
SELECT FirstName,
LastName,
ColumnName,
ColumnValue,
TransID
FROM cte t1
WHERE EXISTS ( SELECT *
FROM cte t2
WHERE t1.Rnk1 = t2.Rnk1
AND t1.ColumnName = t2.ColumnName
AND t2.Rnk2 > 1 )
) t
PIVOT
(
MAX(ColumnValue)
FOR TransID IN ([80071], [80072])
) p
this could most likely be done dynamically, but I'm not sure we have enough information to do that for you.