Creating a correlation matrix in SQL Server

Question

I'm trying to get a correlation matrix in SQL Server and my data is in a table the following way:

RptLOB1     RptLOB2   Correlation
AE          AE             1
Bail        AE            0.35
Commercial  Bail          0.25
Commercial  AE            0.15

...and so on.

I want to write a code so my output looks the following way:

            AE     Bail   Commercial
AE          1      0.35      0.15
Bail        0.35    1        0.25
Commercial  0.15   0.25       1

Order of the RptLOB doesn't matter as long as the order is the same from top to bottom and left to right on top. I've been trying to find a way to approach this and I'm not quite sure what the best way is. I was thinking using PIVOT but that will not output the RptLOB's on top (they will be considered as columns in the table).

EDIT:

This output is going to be inserted in another table like so:

col1             col2        col3                            col4        col5              

Generic
Company Inputs   Insurance   Stochastic Model Correlations   Exposure    Correlation Matrix
                 AE          Bail                            Commercial
AE               1           0.35                            0.15
Bail             0.35        1                               0.25
Commercial       0.15        0.25                            1

@OMGPonies Well the only thing I could think of was pivoting the row values to create that top row but if I do that the row values become column headers and that's not what I want. I'm not sure how to approach is so that doesn't happen and I can create the matrix... — Kristina, Oct 01 '12 at 13:53
I think this is going to be very hard using pure SQL. I suggest trying other tools, like R programming language. — jrara, Oct 01 '12 at 13:56
If you don't want the columns to be column headers, you have a problem in that the data types don't match. — podiluska, Oct 01 '12 at 13:58
It wouldn't be a big deal to use R programming but I need to write a code for what I'm doing in SQL. Would I be able to create a temp table with 46 columns (Number of RptLOB's I have) and pivot those RptLOB's to 1 row? — Kristina, Oct 01 '12 at 14:01

Taryn · Accepted Answer · 2012-10-01T15:24:14.557

You can use a PIVOT for this. If you know the number of columns you have to transform, then you can use a static version:

select *
from 
(
  select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
  from yourtable
  union all
  select RPTLOB2, RptLOB1, Correlation
  from yourtable
  union all
  select distinct RptLOB1, RptLOB1, 1.0
  from yourtable
) x
pivot
(
  max(Correlation)
  for RPTLOB2 in ([AE], [Bail], [Commercial])
) p;

see SQL Fiddle with demo

If you have an unknown number of values to correlate, then you will want to use a dynamic version:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct  ',' 
                      + quotename(RptLOB1)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select RptLOB1, '+@colspivot+ '
     from 
     (
       select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
       from yourtable
       union all
       select RPTLOB2, RptLOB1, Correlation
       from yourtable
       union all
       select distinct RptLOB1, RptLOB1, 1.0
       from yourtable
      ) x
      pivot
      (
        max(Correlation) 
        for RPTLOB2 in ('+ @colspivot +')
      ) p'

exec(@query)

see SQL Fiddle with demo

Edit - based on your comments, if you want the column headers in another row, then you can use the following:

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @colsRow as  NVARCHAR(MAX),
    @colsConverted as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT distinct  ',' 
                      + quotename(RptLOB1)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsRow = STUFF((SELECT distinct  ', ''' 
                      + RptLOB1 + ''' as ' + RptLOB1
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsConverted
      = STUFF((SELECT distinct  ', CAST(' 
                + quotename(RptLOB1) 
                 + ' as varchar(50))'
                from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select ''RptLOB1'' as RptLOB1, 
      '+ @colsRow + '
     union all 
     select RptLOB1, '+ @colsConverted+ '
     from 
     (
       select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
       from yourtable
       union all
       select RPTLOB2, RptLOB1, Correlation
       from yourtable
       union all
       select distinct RptLOB1, RptLOB1, 1.0
       from yourtable
      ) x
      pivot
      (
        max(Correlation) 
        for RPTLOB2 in ('+ @colspivot +')
      ) p'

exec(@query)

see SQL Fiddle with demo

Thank you so much! Is there any way to display the column header (since it contains the other RptLOB's)? This is eventually going to be done in dynamic sql. — Kristina, Oct 01 '12 at 14:21
@Kristina I am not sure what you mean by column header? The column headers are retrieved in the dynamic sql when getting the value for `@colspivot` — Taryn, Oct 01 '12 at 14:23
@bluefeet Eventually I'm going to write a dynamic sql statement so it spits out the whole correlation table and I want it to display the column names. Basically, this correlation matrix is going to be within another table. — Kristina, Oct 01 '12 at 14:29
@Kristina I don't understand what you are asking, can you post a sample of the final result you need? — Taryn, Oct 01 '12 at 14:31
@Kristina do you want the columns headers as a row in the table? — Taryn, Oct 01 '12 at 15:10
@bluefeet yes that way those will show up in my correlation matrix display. — Kristina, Oct 01 '12 at 15:15
@bluefeet Thank you so much for your help. This has definitely helped me broaden my SQL horizons :) — Kristina, Oct 01 '12 at 15:54

score 0 · Answer 2 · answered Oct 01 '12 at 13:58

The problem is that your data is not complete enough. So, enhance it with what you need:

with d as (
    select RptLOB1, RptLOB2, Correlation from t union all
    select RptLOB2, RptLOB1, Correlation from t union all
    select distinct RptLob1, RptLob1, 1.0 from t
)
select RptLOB1, RptLOB2, Corr
from d
pivot (max(correlation) for val in ('AE', 'Bail', 'Commercial')) as corr

If you don't know the names of all the values, then you will need dynamic SQL for the general case.

Creating a correlation matrix in SQL Server

2 Answers2

see SQL Fiddle with demo

see SQL Fiddle with demo

see SQL Fiddle with demo

Linked