CREATE TABLE [tkcsapp].[MARR_LOCATION1](
	[NAME] [nvarchar](10) NULL,
	[ID]   [numeric](4, 0) NULL,
	[Rate] [nvarchar](10) NULL,
	[TYPE] [nvarchar](10) NULL
	
) ON [PRIMARY]
CREATE TABLE [tkcsapp].[MARR_LOCATION2](
	[NAME] [nvarchar](10) NULL,
	[ID]   [numeric](4, 0) NULL,
	[Rate] [nvarchar](10) NULL,
	[TYPE] [nvarchar](10) NULL
	
) ON [PRIMARY]
INSERT INTO [tkcsapp].[MARR_LOCATION1]
    	(name, ID,Rate,TYPE)
    	VALUES ('JOHN', '1111','10.5','F')
INSERT INTO [tkcsapp].[MARR_LOCATION2]
    	(name, ID,Rate,TYPE)
    	VALUES ('JOHN', '1111','11.5','P')


Select a.name,b.name, a.id,b.id,a.rate,b.rate,a.type,b.type 
from [tkcsapp].[MARR_LOCATION1] a
full outer join [tkcsapp].[MARR_LOCATION2] b
on  a.ID = B.ID

name       name       id                                      id                                      rate       rate       type       type
---------- ---------- --------------------------------------- --------------------------------------- ---------- ---------- ---------- ----------
JOHN       JOHN       1111                                    1111                                    10.5       11.5       F          P

A_name      B_name     A_id         Desc Field   Desc Descreption
---------- ---------- ---------- -------------- -------------    
JOHN       JOHN       1111         Rate          10.5,11.5       
JOHN       JOHN       1111         Type          F, P            

quote:

---

Originally posted by emyk

Thank you so much James!
one more question, I only need to show if there is a mismatch. if I have a row that matches with a row from the other table i need to exclude it from the return.
I was thinking to use a case statement to compare each columns from both tables under the select statement.
Is there any other efficient way to do this.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by emyk

Thank you so much James!
one more question, I only need to show if there is a mismatch. if I have a row that matches with a row from the other table i need to exclude it from the return.
I was thinking to use a case statement to compare each columns from both tables under the select statement.
Is there any other efficient way to do this.

---

what all fields you want to compare on? you should add a where clause to check the condition like

WHERE a.Col <> b.Col

in query inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT * FROM
(	
	Select a.name AS A_Name,b.name AS B_Name, a.id AS A_id,
		COALESCE(a.rate+N',','') + COALESCE(b.rate,'') AS Rate,
		COALESCE(a.type+N',','') + COALESCE(b.type,'') AS Type,
                COALESCE(a.JobCode+N',','') + COALESCE(b.JobCode,'') AS JobCode
	from [tkcsdb].[tkcsapp].[MARR_LOCATION1] a
	full outer join [tkcsdb].[tkcsapp].[MARR_LOCATION2] b
	on  a.ID = B.ID
)s UNPIVOT ([Desc Descreption] FOR [Desc Field] IN ([Rate],[Type],[JobCode]))U

SELECT * FROM
(	
	Select a.name AS A_Name,b.name AS B_Name, a.id AS A_id,
		COALESCE(a.rate+N',','') + COALESCE(b.rate,'') AS Rate,
		COALESCE(a.type+N',','') + COALESCE(b.type,'') AS Type,
        CAST(COALESCE(a.JobCode+N',','') + COALESCE(b.JobCode,'')  AS VARCHAR(256)) AS JobCode
	from [tkcsdb].[tkcsapp].[MARR_LOCATION1] a
	full outer join [tkcsdb].[tkcsapp].[MARR_LOCATION2] b
	on  a.ID = B.ID
)s UNPIVOT ([Desc Descreption] FOR [Desc Field] IN ([Rate],[Type],[JobCode]))U