I find it hard to think of a scenario where this would be the optimal choice.

I'd be interested to know the business requirements as Ninja requested.

Here's the situation I'm in:

I have the same 10 fields coming from two different systems, system1 and system2. I would like to do a comparison of all of the fields...

Lowell (11/4/2011)

---

i think you want to look at the EXCEPT operator;

if all the columns are the same, it might look like this:

--find what doesn't match from Table2

SELECT PK,ColumnList FROM Table1

EXCEPT

SELECT...

That's what I thought. I'm running under "NT AUTHORITY\NETWORKSERVICE" but apparently you can't add permissions to folders in windows using that verbiage. I think it uses "Network Service"

Thanks,

Mike

Jeff Moden (7/8/2011)

---

Since the requirements are all over hell's half-acre, let's review.

1. We have two tables which will ALWAYS have a corresponding row in the other table.

2. If...

opc, thanks for laying all of that out.

Nevyn, yes, I'd like to see all NULLs if wherever there aren't hobbies (even if there's 0/4 hobbies populated I'd like to see...

opc.three (7/8/2011)

---

mikes84 (7/8/2011)

---

Regarding these two requirements mikes84:

1) Do you want duplicates shown in the results?

2) Does order matter?

Using our two tables hobby1 and hobby2 here is an example that should...

tommyh (7/8/2011)

---

Modding my code so it should do what you now want.

SELECT t.id,

RTRIM(SUBSTRING(t.s, 1, 36)),

...

Regarding these two requirements mikes84:

1) Do you want duplicates shown in the results?

2) Does order matter?

Using our two tables hobby1 and hobby2 here is an example that should answer both...

opc, what about this for the PARSENAME method?

SELECT h1.id,

REVERSE(PARSENAME(REVERSE(REPLACE(RTRIM(

CASE WHEN h1.hobby1 IS NULL THEN '' ELSE h1.hobby1 + ' ' END +

CASE WHEN h1.hobby2 IS NULL THEN ''...

OK, how about this?

;WITH data(hobby1,hobby2,hobby3,hobby4)

AS

(

SELECT 'basketball',NULL,NULL,NULL UNION ALL

SELECT NULL,'basketball',NULL,NULL UNION ALL

...

Actually that won't work. I think some derivative of that would though.

Yeah, I sure wasn't expecting so many answers. I started to revisit the problem again. What do you think of this (also not that pretty):

SELECT

ISNULL(REVERSE(PARSENAME(REVERSE(REPLACE(ISNULL(hobby1, ' ') + ISNULL(hobby2, '...

I suppose I could union the fields between the two tables and remove the NULLs that way, then convert the rows into columns if that's what you mean.

Thank you, all!

Kramasawy, I would say that it was fairly even mixture (at least for me) of practical questions and theoretical. Some of the material I never knew prior to...