Joining Datasets Side by Side, in Their Natural Order When There’s No Common Key

1024px-Pair_of_Large_Animals.jpg

Here’s a little something that came up the other day, we’ve got a couple of tables which we need to join together but there’s no common key to join on.  How do we go about attacking this?

As an example consider the following, we’ve got two comma delimited strings that we need to join together.  We can convert them into a table using STRING_SPLIT (or if you’re still pre-SQL 2016, you could use our own fn_SplitString from the Undercover Toolbox) but what are we going to do about joining them so that the first row is joined to the first row, the second row to the second and so on (we’re not trying to get the cartesian product here)?

</pre>
SELECT value FROM STRING_SPLIT('Freddie,Brian,Roger,John',',')

SELECT value FROM STRING_SPLIT('Mercury,May,Taylor,Deacon',',')
<pre>

 

 

We haven’t got any key values to join on, we just need to join the tables in the order that they’re in.  Could we perhaps just assign the tables a row number?  How about something like…


SELECT value, ROW_NUMBER() OVER()
FROM STRING_SPLIT('Freddie,Brian,Roger,John',',')

Msg 4112, Level 15, State 1, Line 5
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.

hmmmmm… not quite, ROW_NUMBER has to order the data.  The problem that we’ve got now is that we don’t want to order the values as that’ll mess up our data, we need them to be in their natural order.

For the answer to this I have to tip my hat to Pinal Dave for a very cool little trick that I kicked myself for not thinking of.

You can get around the need to order the rows by simply using the order by statement ORDER BY (SELECT 1).

Lets just check that out and see what happens…


SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownumber
FROM STRING_SPLIT('Freddie,Brian,Roger,John',',')

SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownumber
FROM STRING_SPLIT('Mercury,May,Taylor,Deacon',',')

 

Would you look at that?!  We’ve now got row numbers in the natural order of the tables, with that it should be an easy thing to just join on rownumber.

Let’s have a look…


SELECT forename.value AS forename, surname.value AS surname
FROM
(SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownumber
FROM STRING_SPLIT('Freddie,Brian,Roger,John',',')) AS forename
JOIN
(SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownumber
FROM STRING_SPLIT('Mercury,May,Taylor,Deacon',',')) AS surname
ON forename.rownumber = surname.rownumber

join5

And there we have it folks, that’s how you join two tables side by side when there’s no common key value.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: