There's a huge performance problem associated with the code in the blog. It has an "Accidental CUBE Join" (Also known as a Double Cross Join) it it. If you use the test data and the code provided in that article and run it with the Actual Execution Plan turned on, you'll find an arrow which shows that 27 rows (3 sets * 3 rows * 3 unpivots) have been spawned interally. With the addition of a 4th row, 36 internal rows are generated. With 1000 rows, 9000 internal rows would be generated. In other words, the code will take 9 times longer and use 9 times the number of resources than it needs to.

Further, as you've already found out, the code also relies on joins that you may not have available.

Try something like this, instead... it only creates the same number of internal rows as what will appear in the final output and it was done without the ID column that you say you don't have... and the code is a whole lot less complex, as well. This code has been tested against the code provided in the blog article you provided the link for.

SELECT s.Product, d.Supplier, d.City

FROM dbo.Suppliers s

CROSS APPLY (

SELECT Supplier1, City1 UNION ALL

SELECT Supplier2, City2 UNION ALL

SELECT Supplier3, City3

) d (Supplier, City)

GO

Hat's off to Paul White for first showing me this method.