you might want to look into a FULL JOIN of all four tables.

Hi,

please help us help you and provide ready to use sample data and expected result as described in the first article referenced in my signature.

I agree it looks like homework.

But the funny part is: the expected result is wrong based on the given requirement (at least form my point of view)... :w00t:

@daglugub: what...

I'd use the cte approach. I'm confident it can be compressed a little further but this would be my starting point:

WITH cte_originalquery AS

(

SELECT 1 AS Number UNION ALL

SELECT 2 AS...

Thank you for your reply. I'll give it a try even though I don't think it will make a difference for two reasons:

a) the proxy I'm using is based on...

You might want to look into the Dynamic Cross Tab approach as referenced in my signature.

Might be the less strugglig way to achieve what you're looking for.

As a side note:...

You could use the OUTPUT clause in the INSERT statement and capture the primary key together with the values inserted in a table variable.

See BOL (BooksOnLine) for an example (keyword...

I'll bring some hay for the hippo!!

Here's the link to the solution I posted.

The OP's answer?

Neither "This will work." nor "The logic should be..."

But instead: "Where should I put this query in my original SQL...

You still havent learned that we'd like to know your EXPECTED result. NOT the ACTUAL result which we all know is not what you're looking for.

PLEASE, PLEASE, PLEASE tell us...

You still haven't learned that we don't sit around a glass ball and figure out what "it doesn't work" means in your case.

I'm also surprised you continue to use a...

Jeff, I'm feeling today is one of those days where I'm just getting ignored... 😉

Ok, here's the only way I can think of: to use the "Quirky update" as described in Jeff Modens article[/url]

It is absolutely required to read the article and fully understand...

Per definition a CASE function will return the result based on the first match.

There's very little we can do. The requirement doesn't make much sense to me.

Please provide table def...

Neither one of your statements "I don't get the result" nor "i get the substring error" do really help us to narrow down the issue. SQL Server is kind enough...