SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to show multiple columns values in one column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emyk
Yak Posting Veteran

51 Posts

Posted - 03/03/2013 :  20:21:27  Show Profile  Reply with Quote
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

3661 Posts

Posted - 03/03/2013 :  20:45:05  Show Profile  Reply with Quote
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
Go to Top of Page

emyk
Yak Posting Veteran

51 Posts

Posted - 03/03/2013 :  20:48:12  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 03/03/2013 :  20:53:18  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2013 :  22:40:53  Show Profile  Reply with Quote
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/

Go to Top of Page

emyk
Yak Posting Veteran

51 Posts

Posted - 03/06/2013 :  18:42:47  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 03/06/2013 :  19:11:59  Show Profile  Reply with Quote
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
Go to Top of Page

emyk
Yak Posting Veteran

51 Posts

Posted - 03/06/2013 :  20:16:58  Show Profile  Reply with Quote
James - That took care of it, I put the CAST in all the fields per your suggestion.

Thank yo so much again!!
Alex
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  23:15:19  Show Profile  Reply with Quote
looks like unicode datatype to me as you're concatenating N',' to values

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000