| Author |
 Topic  |
|
|
emyk
Starting Member
37 Posts |
 Posted - 03/03/2013 : 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] a
full outer join [tkcsapp].[MARR_LOCATION2] b
on a.ID = B.ID
I get the below row from the above query
name 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
|
Edited by - emyk on 03/03/2013 20:28:31
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 03/03/2013 : 20:45:05
|
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 |
Edited by - James K on 03/03/2013 20:45:33 |
 |
|
|
emyk
Starting Member
37 Posts |
Posted - 03/03/2013 : 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.
|
Edited by - emyk on 03/03/2013 21:01:11 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 03/03/2013 : 20:53:18
|
| You are very welcome - glad to help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/03/2013 : 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 like
WHERE a.Col <> b.Col
in query inside
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
emyk
Starting Member
37 Posts |
Posted - 03/06/2013 : 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 like
WHERE a.Col <> b.Col
in query inside
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 39
The 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 |
Edited by - emyk on 03/06/2013 19:02:10 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 03/06/2013 : 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
Starting Member
37 Posts |
Posted - 03/06/2013 : 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
India
47023 Posts |
Posted - 03/06/2013 : 23:15:19
|
looks like unicode datatype to me as you're concatenating N',' to values
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
How to show multiple columns values in one column
