Join Help

Question

Join Help

patla4u

SSCrazy

Points: 2103
More actions
November 30, 2017 at 11:53 am

#330176

I would like to get below 3 raw as result.
12    67    601006    1001072
12    67    605059    1001075
12    67    606722    1001083
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

What defines which row belongs to which? For example, why not:
12 67 605059 1001083
Is it ascending order for both sides?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Problem. How does the query decide which row goes with which other row? You'll need some column that provides that guidance, that is not shown in your picture. The reason that I ask is that SQL Server does not have any inherent ORDER to a table. It's never guaranteed that a read of all the rows will occur in the exact same order they were inserted in. That's why we need to know. You may be able to use a datetime column that reflects the exact order you're showing us, but it would have to already exist, or get manually created.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Thom A SSC Guru Points: 99126 More actions · Answer 3

Based the guess that the JOIN is based on ASCENDING order on both sides on the coin, this'll be enough to get your way:

WITH ACA AS (
  SELECT *,
    ROW_NUMBER() OVER (ORDER BY ACA) AS RN
   FROM YourTable
   WHERE ACA IS NOT NULL),
MET AS (
   SELECT *,
    ROW_NUMBER() OVER (ORDER BY MET) AS RN
   FROM YourTable
   WHERE MET IS NOT NULL)
SELECT *
FROM ACA 
  JOIN MET ON ACA.RN = MET.RN;

(Untested)

If not, then I'm not going to bother repeating myself and Steve again. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

patla4u SSCrazy Points: 2103 More actions · Answer 4

Thank you.

That should work. Order is doesn't matter.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Thom A - Thursday, November 30, 2017 12:02 PM
Based the guess that the JOIN is based on ASCENDING order on both sides on the coin, this'll be enough to get your way:
WITH ACA AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY ACA) AS RN
FROM YourTable
WHERE ACA IS NOT NULL),
MET AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY MET) AS RN
FROM YourTable
WHERE MET IS NOT NULL)
SELECT *
FROM ACA
JOIN MET ON ACA.RN = MET.RN;
(Untested)
If not, then I'm not going to bother repeating myself and Steve again. 🙂

Don't lose that crystal ball!