Get the sum of squares for each permutation

create table t(id int , x1 int,x2 int,x3 int ,x4 int ,x5 int );

insert into t
select 5555,4,3,5,null,null union all
select 6666,3,4,7,8,null union all
select 7777,2,2,2,2,2;

So i have this data , lets take id 5555 for example , i need to get those sum of squares for every combination i can get from the 3 values 5555 has - 4,3,5 .

Means :

4^2 | 3^2 + 5^2
4^2 + 3^2 | 5^2
4^2 + 5^2 | 3^2

The " | " is just a separator between the 2 combinations .

How can i do that ?

If you want 1 row per id, you could do

``````select id
,x1*x1 as result1
,x2*x2+x3*x3 as result2
,x1*x1+x2*x2 as result3
,x3*x3 as result4
,x1*x1+x3*x3 as result5
,x2*x2 as result6
from t
;
``````

which will give you

``````id      result1 result2 result3 result4 result5 result6
5555         16      34      25      25      41       9
6666          9      65      25      49      58      16
7777          4       8       8       4       8       4
``````

Doing exactly the same using power function

``````select id
,power(x1,2) as result1
,power(x2,2)+power(x3,2) as result2
,power(x1,2)+power(x2,2) as result3
,power(x3,2) as result4
,power(x1,2)+power(x3,2) as result5
,power(x2,2) as result6
from t
;
``````

Now - if you want 3 rows per id (as you indicated in your question), you could do

``````select a.id
,case b.n
when 1 then x1*x1
when 2 then x1*x1+x2*x2
when 3 then x1*x1+x3*x3
end as result1
,case b.n
when 1 then x2*x2+x3*x3
when 2 then x3*x3
when 3 then x2*x2
end as result2
from t as a
outer apply (values(1),(2),(3)) as b(n)
order by a.id
;
``````

which will give you

``````id   result1 result2
5555      16      34
5555      25      25
5555      41       9
6666       9      65
6666      25      49
6666      58      16
7777       4       8
7777       8       4
7777       8       4
``````

Or you could do

``````select id
,result1
,result2
from (          select id
,x1*x1 as result1
,x2*x2+x3*x3 as result2
from t
union all select id
,x1*x1+x2*x2 as result1
,x3*x3 as result2
from t
union all select id
,x1*x1+x3*x3 as result1
,x2*x2 as result2
from t
) as a
order by id
;
``````

wow ! thank you so much its really helped me .

Now i need to take the minimum result value for each id , how can i get that ?

for 5555 - 9
for 6666 - 9
for 7777 - 4

Maybe this:

``````select id
,min(result) as result
from (select a.id
,case b.n
when 1 then x1*x1
when 2 then x1*x1+x2*x2
when 3 then x1*x1+x3*x3
when 4 then x2*x2+x3*x3
when 5 then x3*x3
when 6 then x2*x2
end as result
from t as a
outer apply (values(1),(2),(3),(4),(5),(6)) as b(n)
) as a
group by id
;
``````

can we do it without cross apply ?

Sure, but why?

dont alowed to use it hhh

That's a strange statement. I could understand it, if you're not allowed to use cursors or nolock.

Anyway - you could do

``````select id
,min(result) as result
from (select a.id
,case b.n
when 1 then x1*x1
when 2 then x1*x1+x2*x2
when 3 then x1*x1+x3*x3
when 4 then x2*x2+x3*x3
when 5 then x3*x3
when 6 then x2*x2
end as result
from t as a
inner join (values(1),(2),(3),(4),(5),(6)) as b(n)
on 1=1
) as a
group by id
;
``````

or

``````select id
,min(result) as result
from (select a.id
,case b.n
when 1 then x1*x1
when 2 then x1*x1+x2*x2
when 3 then x1*x1+x3*x3
when 4 then x2*x2+x3*x3
when 5 then x3*x3
when 6 then x2*x2
end as result
from t as a
,(values(1),(2),(3),(4),(5),(6)) as b(n)
) as a
group by id
;
``````