Thanks guys for your replies. I appreciate your effort very very much.
Now to the results:

I've tried both Phil's and Jeff's solutions. Phil's solution works fine, Jeff's on the other hand...

Yes, that is exactly what I want.

thx for reply

Hi, me again

After doing some gym i came up with the following solution:

;with aa as

(select

id,

record_type,

old.property,

old.value

FROM

(select

id,

record_type,

convert(varchar(250),sex) sex,

convert(varchar(250),age) age

from

#test) data

UNPIVOT

(value FOR property IN (sex,age)) old)

select

old.id,

old.property,

old.value old_value,

new.value new_value

from

aa old

inner...

Using CTE on large queries can be a real performance bottleneck.

Let alone using nested CTEs.

I always try to recognize the query that is or may become a part of...

I prefer using merge statement combined with output which enables me to perfrorm additional inserts.

Below is an example of such statement; my task is to make an update on...

Thx again, John

What you say is true.

Guess I'll just have to use pure math algorythm first.

Many thanks.

Regards,

Mark

John, thx for your reply.

What you say does help a lot. The problem hovewer remains.

I tried your solution in real life and it didn't help me much.

There are still more...

My opinion: MSSQL window function are a great way to manipulate data, promoting the set based approach

--An example of how to simply remove duplicates from the data table using window...

Thanks for letting me know Squared.

I checked the UDF's performance on 100K records (via a view) and managed to retreive data in 50s. Id does seem a lot, but in...

Squared & Magoo

Thanks both for your replies.

I did find something myself.

Here is the code:

";with aa as

(select * from __xxx where idd=1),

cte ( idd, ime, [ime_list], length, stev )

...

Hi Jeff

First I'd like to thank you for the given solution. I tried it out in every way I could think of and it now works perfectly.

You say: »Where clauses...

Hello Jeff and thanks for your reply.

The story is simple:

I want to re-code an existing function whics contains a while loop.

I don't have any delimiter whatsoever; the only...

Thy 4 ur reply.

I get the same execution plans for last two executions. What bothers me is the "segment" operation; it displays cost 493815% ??

Anyhow, the performance is still poor....