Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
emyk
Yak Posting Veteran
57 Posts |
Posted - 2013-03-03 : 20:21:27
|
I have two tables joined on Full outer join (two tables with same field columns). Then I need to compare the two tables columns and show if the columns on the two tables don't match.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] afull outer join [tkcsapp].[MARR_LOCATION2] bon a.ID = B.ID I get the below row from the above queryname name id id rate rate type type---------- ---------- --------------------------------------- --------------------------------------- ---------- ---------- ---------- ----------JOHN JOHN 1111 1111 10.5 11.5 F P the desired output is the following:A_name B_name A_id Desc Field Desc Descreption---------- ---------- ---------- -------------- ------------- JOHN JOHN 1111 Rate 10.5,11.5 JOHN JOHN 1111 Type F, P |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 20:45:05
|
[code]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 from [tkcsapp].[MARR_LOCATION1] a full outer join [tkcsapp].[MARR_LOCATION2] b on a.ID = B.ID)s UNPIVOT ([Desc Descreption] FOR [Desc Field] IN ([Rate],[Type]))U[/code] |
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 2013-03-03 : 20:48:12
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 20:53:18
|
You are very welcome - glad to help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-03 : 22:40:53
|
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 likeWHERE a.Col <> b.Colin query inside------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 2013-03-06 : 18:42:47
|
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 likeWHERE a.Col <> b.Colin query inside------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Ok,now I am attempting to add more columns to my querry, but I am getting the below error to some of the columns. Here is one that gives me the below error. My new column is column (nvarchar(6)null)Msg 8167, Level 16, State 1, Line 39The type of column "JobCode" conflicts with the type of other columns specified in the UNPIVOT list.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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-06 : 19:11:59
|
It may have to do with the data type of JobCode. What is the data type? You might try the following; if that does not fix it, examine the data types of rate, type and JobCode and cast them all to the same type if they are not the same type: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 |
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 2013-03-06 : 20:16:58
|
James - That took care of it, I put the CAST in all the fields per your suggestion. Thank yo so much again!!Alex |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 23:15:19
|
looks like unicode datatype to me as you're concatenating N',' to values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|