Insert values from multiple column in single column-CodePudding

I am comparing data from two tables - Table A and Table B. I would like to create temporary table or view which will only return the mismatched records and store the mismatched messages from every column into single column .

I only have read permission to Database so is there a way to get the desired output creating Views/?

Data looks like this:

FirstNameCheck	EmailCheck	TelephoneCheck	TableAFirstName	TableAEmail	TableATelephone	TableBFirstName	TableBEmail	TableBTelephone
MATCHED	MATCHED	MISMATCHED IN TELEPHONE	Pano	[email protected]	123222	Pano	[email protected]	NULL
MATCHED	MISMATCHED IN EMAIL	MISMATCHED IN TELEPHONE	Brian	[email protected]	NULL	Brian	NULL	123456
MATCHED	MATCHED	MATCHED	William	NULL	132777	William	NULL	132777
MATCHED	MATCHED	MISMATCHED IN TELEPHONE	Bill	NULL	145678	Bill	NULL	145669
MATCHED	MATCHED	MISMATCHED IN TELEPHONE	Robin	NULL	132444	Robin	NULL	132456

Expected result:

Error_Message	TableAFirstName	TableAEmail	TableATelephone	TableBFirstName	TableBEmail	TableBTelephone
MISMATCHED IN TELEPHONE	Pano	[email protected]	123222	Pano	[email protected]	NULL
MISMATCHED IN EMAIL--MISMATCHED IN TELEPHONE	Brian	[email protected]	NULL	Brian	NULL	123456
MISMATCHED IN TELEPHONE	Bill	NULL	145678	Bill	NULL	145669
MISMATCHED IN TELEPHONE	Robin	NULL	132444	Robin	NULL	132456

Query which I tried and got stuck to get new column with error_messages

SELECT column1, 2
FROM (query-which gives the sample data) 
WHERE (FirstNameCheck = 'MISMATCHED IN FIRST NAME' OR EmailCheck = 'MISMATCHED IN EMAIL' OR TelephoneCheck = 'MISMATCHED IN TELEPHONE')

Can someone please help me how to do this?

Thanks in advance

CodePudding user response：

I used CASE expressions and CONCAT_WS to accomplish this. Long story short, I nullify "match" instances and then concatenate the mismatches into one column.

Note: CONCAT_WS only works on SQL Server 2017 or later. If you're on an earlier version of SQL Server just use the regular CONCAT function.

SQL:

SELECT 
  CONCAT_WS(' -- ', [FirstNameCheck_MisMatch], [EmailCheck_MisMatch], [TelephoneCheck_MisMatch]) AS Error_Message, 
  a.[TableAFirstName], 
  a.[TableAEmail], 
  a.[TableATelephone], 
  a.[TableBFirstName], 
  a.[TableBEmail], 
  a.[TableBTelephone] 
FROM 
    (SELECT 
      CASE WHEN a.[FirstNameCheck] <> 'MATCHED' THEN a.[FirstNameCheck] END AS [FirstNameCheck_MisMatch], 
      CASE WHEN a.[EmailCheck] <> 'MATCHED' THEN a.[EmailCheck] END AS [EmailCheck_MisMatch], 
      CASE WHEN a.[TelephoneCheck] <> 'MATCHED' THEN a.[TelephoneCheck] END AS [TelephoneCheck_MisMatch], 
      a.[FirstNameCheck], 
      a.[EmailCheck], 
      a.[TelephoneCheck], 
      a.[TableAFirstName], 
      a.[TableAEmail], 
      a.[TableATelephone], 
      a.[TableBFirstName], 
      a.[TableBEmail], 
      a.[TableBTelephone] 
    FROM 
      ERROR_CHECK a) a
  /* RETURN ONLY RECORDS WHERE MISMATCH HAPPEN */
WHERE 
  COALESCE(a.[FirstNameCheck_MisMatch], a.[EmailCheck_MisMatch], a.[TelephoneCheck_MisMatch]) IS NOT NULL;

Result:

Error_Message	TableAFirstName	TableAEmail	TableATelephone	TableBFirstName	TableBEmail	TableBTelephone
MISMATCHED IN TELEPHONE	Pano	[email protected]	123222	Pano	[email protected]	(null)
MISMATCHED IN EMAIL -- MISMATCHED IN TELEPHONE	Brian	[email protected]	(null)	Brian	(null)	123456
MISMATCHED IN TELEPHONE	Bill	(null)	145678	Bill	(null)	145669
MISMATCHED IN TELEPHONE	Robin	(null)	132444	Robin	(null)	132456

SQL Fiddle: http://sqlfiddle.com/#!18/2a640e/11