Join and a Pivot statement SQL Server

Question

I want to pivot and join to select from 3 tables

Table 1: INT,VARCHAR,FLOAT

ID Name  value
---------------------------
1   a1  32116580
2   a2  50785384
3   a3  54327508
4   a4  61030844

Table 2: INT, VARCHAR, FLOAT

ID   Name     value
---------------------------
1   x11   61326085092
2   x12   80368184260
3   x13   83023398776
4   x14   91144307692
5   x22   95486535484
6   x23   90357090612
7   x24   100588807668
8   x33   707811916752
9   x34   93128452928
10  x44   84566653668

Table 3: INT, VARCHAR, FLOAT

ID   Name     value
---------------------------
1   q1   61326085092
2   q2   95486535484
3   q3   707811916752
4   q4   84566653668

output table:

column1              column2            column3             column4
--------------------------------------------------------------------------
  a1*a1/(q1+q1+x11)  a1*a2/(q1+q2+x12)    a1*a3/(q1+q3+x13)     a1*a4/(q1+q4+x14)
  null               a2*a2/(q2+q2+x22)    a2*a3/(q2+q3+x23)     a2*a4/(q2+q4+x24)
  null               null                 a3*a3/(q3+q3+x339     a3*a4/(q3+q4+x34)
  null               null                 null                  a4*a4/(q4+q4+x44)

(I'm putting the 'Name' of the column of the 3 different tables instead of numbers)

How to do this?
I guess I have to do two pivots? and unpivot?...

Well do not how to complete it..

SELECT *
  FROM (
        SELECT
         t1.a1,
         t1.a2,
         t2.x,
         t3.q
         FROM table1 t1
         INNER JOIN table2 t2
         ON t1.id = t2.id
         ...
   ) Output
   PIVOT (
      name IN (
            ...
      PIVOT(name ... )
      )
  ) PivotTable

UPDATE Previously I have *'s I have changed it to division and sum, the *'s were just an example,

This site can help you ? http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx — Michaël, Mar 06 '11 at 18:43
This is a fixed column output? No adding columns as you add rows? — gbn, Mar 06 '11 at 19:50
well, The output is a symmetric matrix, as we add a row we can add a column... — edgarmtze, Mar 06 '11 at 19:55
Are you after ACCURATE results for the series of *'s? The numbers are astronomical given your sample and will not fit in any SQL Server datatype — RichardTheKiwi, Mar 06 '11 at 20:51
How do you represent row 11? x111 is row 11 col 1 or row 1 col 11? Do the IDs and Names always match, i.e. A1 = ID:1, A2 = ID:2 — RichardTheKiwi, Mar 06 '11 at 21:02
For table1 where the names are `a1, a2,a3,a4`, yes, the ID's match them all, also for table 3 `q1,q2,q3,q4` match ID's, for Table 3, they do not match perfect with the ID... `ID 1->x11,..., ID 5->x22,..., ID 8->x33,..., ID 10->x44` — edgarmtze, Mar 06 '11 at 21:05
ID10=x44 is not the problem, the question was, what is the xcode for row 11, column 1, and for row 1, column 11? It would be better if we simply restricted this to max row=9, max col=9 — RichardTheKiwi, Mar 06 '11 at 21:14
I'm going to try one last time. ROW 11 (2-digits), column 1. ROW 1, column 11 (2-digits). If the IDs are supposed to order them triangularly in order, then the NAMES should mean nothing, in which case I'd drop the name column and derive it from the ID alone. Row1, column11 would be ID=11 by definition, and row2, column11 would be ID=21 (11 from row 1, 10 from row 2). — RichardTheKiwi, Mar 06 '11 at 21:30
I think I get the idea of what you're asking...(sorry if it is not) Table 2 codifies an upper triangular matrix, this is: `the first 4 rows would represent the first row in the upper matrix`, `x11,x12,x13,x14` is the first row, then the next 3 rows represent the next row of the upper matrix `x22,x23,x24`, then the next 2, the third row of the upper matrix `x33,x34`, and `x44` is the last row of the upper matrix. The upper triangular matrix is codified as a 3 field column table where the ID represents the location of `xij` in the upper matrix . — edgarmtze, Mar 06 '11 at 21:41
Also Table 3 codifies the diagonal of the upper triangular matrix — edgarmtze, Mar 06 '11 at 21:47
The column name can be dropped and derivate value from ID as you suggest. — edgarmtze, Mar 06 '11 at 21:56
How do you differentiate between x-11-1 and x-1-11 (without the dashes), in your `xij` naming scheme? — RichardTheKiwi, Mar 06 '11 at 21:59
Will tables 1,2,3 always be balanced and fully populated? If n=5, rowcount(table1)=5, rowcount(table3)=5, rowcount(table2)=15 — RichardTheKiwi, Mar 06 '11 at 22:01
They are the same, `xij = xji`, because it is a symmetric matrix — edgarmtze, Mar 06 '11 at 22:10
If `n=5`, `rowcount(table1)=5`, `rowcount(table3)=5`, `rowcount(table2)=10`, In general for any n, `rowcount(table1)=n, rowcount(table3)=n, rowcount(table2)=( n*(n-1) ) / 2` — edgarmtze, Mar 06 '11 at 22:12
But a casual observer will not know that for x232, it must represent i=2, j=32 because for an upper triangular symmetric matrix, there is no value for (23,2) — RichardTheKiwi, Mar 06 '11 at 22:24
Now that I have invested time into the question, are you able to reveal what the real life scenario is that would require such a query? — RichardTheKiwi, Mar 06 '11 at 22:42
Yes, you can use this query to do some sort of statistical analysis of a set of data, in fact the 3 tables are just a summarization of a bigger data set. So if using appropiate formula, you can build with this 3 tables descriptions of the big data set. — edgarmtze, Mar 06 '11 at 22:52

RichardTheKiwi · Accepted Answer · 2011-03-06T23:10:54.713

Sample tables

create table Table1(ID int, Name varchar(10), value float)
insert table1 select
1 ,'a1', 32116580 union all select
2 ,'a2', 50785384 union all select
3 ,'a3', 54327508 union all select
4 ,'a4', 61030844

create table Table2(ID int, Name varchar(10), value float)
insert Table2 select
1 ,'x11', 61326085092 union all select
2 ,'x12', 80368184260 union all select
3 ,'x13', 83023398776 union all select
4 ,'x14', 91144307692 union all select
5 ,'x22', 95486535484 union all select
6 ,'x23', 90357090612 union all select
7 ,'x24', 100588807668 union all select
8 ,'x33', 707811916752 union all select
9 ,'x34', 93128452928 union all select
10 ,'x44', 84566653668

create table Table3(ID int, Name varchar(10), value float)
insert Table3 select
1 ,'q1', 61326085092 union all select
2 ,'q2', 95486535484 union all select
3 ,'q3', 707811916752 union all select
4 ,'q4', 84566653668

The query you need, for N = 4. For any other N, just use dynamic SQL to build the query, changing the 2 lines required as indicated by **.

;with coords(i,row,col,total,N) as (
select 1,1,1,N.N*(N.N+1)/2, N.N
from (select count(*) N from table1) N
union all
select i+1,
       case when col+1>N then row+1 else row end,
       case when col+1>N then row+1 else col+1 end,
       total, N
from coords
where i<total
)
select [1],[2],[3],[4] -- **, e.g. ,[5],[6],etc
from
(
    select
        c.row,
        c.col,
        cellvalue= ar.value*ac.value/(qr.value+qc.value+x.value)
    from coords c
    inner join table1 ar on ar.id = c.row
    inner join table1 ac on ac.id = c.col
    inner join table3 qr on qr.id = c.row
    inner join table3 qc on qc.ID = c.col
    inner join table2 x on x.ID = c.i
) p
pivot (max(cellvalue) for col in ([1],[2],[3],[4])) pv   -- **
order by row

Output:

1                      2                      3                      4
---------------------- ---------------------- ---------------------- ----------------------
5606.50338459295       6876.83326310711       2047.51559459649       8269.17991568225
NULL                   9003.55641750708       3087.36780924588       11044.2303130135
NULL                   NULL                   1389.95405212248       3744.35614651666
NULL                   NULL                   NULL                   14681.7678040306

The dynamic version

declare @Sql nvarchar(max)
select @Sql = ISNULL(@sql + ',', '') + QUOTENAME(RIGHT(number,10))
from master..spt_values
where type='P' and number between 1 and (select COUNT(*) From table1)
set @Sql = '
;with coords(i,row,col,total,N) as (
select 1,1,1,N.N*(N.N+1)/2, N.N
from (select count(*) N from table1) N
union all
select i+1,
       case when col+1>N then row+1 else row end,
       case when col+1>N then row+1 else col+1 end,
       total, N
from coords
where i<total
)
select ' + @sql + '
from
(
    select
        c.row,
        c.col,
        cellvalue= ar.value*ac.value/(qr.value+qc.value+x.value)
    from coords c
    inner join table1 ar on ar.id = c.row
    inner join table1 ac on ac.id = c.col
    inner join table3 qr on qr.id = c.row
    inner join table3 qc on qc.ID = c.col
    inner join table2 x on x.ID = c.i
) p
pivot (max(cellvalue) for col in (' + @sql + ')) pv
order by row
option (maxrecursion 0)  -- ! use with caution
'
exec(@sql)

One question, I tried to apply your solution, to theb biggest tables, n = 25, and I get:`Msg 530, Level 16, State 1, Line 2 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.` The query is ok,and it is generated As you said...so this recursion is not going to be defeated right? — edgarmtze, Mar 06 '11 at 23:07
@darkcminor - I added `option maxrecursion` which will get around it. — RichardTheKiwi, Mar 06 '11 at 23:11
@Richard aka cyberkiwi: I know it is kind of other question but using `maxrecursion` tells SQL to keep going...?? — edgarmtze, Mar 06 '11 at 23:14
@darkcminor - recursion is a [general programming] technique to keep going back and redoing some similar thing. If you had a bad query that simply says, `do x, redo (x)` without any variation whatever, you have just created an infinite loop. The code above does actually always terminates after N*(N-1)/2 is reached so it is pretty benign, but I always add the caution when the option is used. If you had N=100000, it will give SQL Server a very large task to process — RichardTheKiwi, Mar 06 '11 at 23:21

Join and a Pivot statement SQL Server

Table 1: INT,VARCHAR,FLOAT

Table 2: INT, VARCHAR, FLOAT

Table 3: INT, VARCHAR, FLOAT

output table:

1 Answers1