SQLTeam.com | Weblogs | Forums

Get the sum of squares for each permutation

tsql
sql2012
sql2008
sql2008r2

#1

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 ?


#2

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
;

#3

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


#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
;

#5

can we do it without cross apply ?


#6

Sure, but why?


#7

dont alowed to use it hhh :slight_smile:


#8

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
;