ma'lumotlarni kovaryans matrisi sifatida ko'rsatish

I have a query that returns the first table below, so three columns, Var1 (variable 1), Var2 & Covariance.

Qanday qilib men xohlayman, agar ma'lumot imkoni bo'lsa, kovaryans matritsasi shaklida qaytariladimi?

 Var1   Var2 Covariance
 ABC    ABC 0.00213334
 DEF    ABC 4.75E-05
 DEF    DEF 0.0015896
 MNO    ABC 0.00012669
 MNO    DEF 0.000278643
 MNO    MNO 0.001189053
 XYZ    ABC 0.000456708
 XYZ    DEF -0.00030231
 XYZ    MNO -0.000390762
 XYZ    XYZ 0.002947633

Natija Kovaryans matritsasini istayman.

        ABC     DEF     MNO     XYZ
    ABC 0.0021  0.0000  0.0001  0.0005
    DEF 0.0000  0.0016  0.0003  -0.0003
    MNO 0.0001  0.0003  0.0012  -0.0004
    XYZ 0.0005  -0.0003 -0.0004 0.0029
1
Bunday o'zgarish odatda dastur darajasida yaxshiroq amalga oshiriladi.
qo'shib qo'ydi muallif Gordon Linoff, manba

6 javoblar

Dynamic pivot:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + var1
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')
select  @sql = '
 select var2, ' + @cols + '
  from  (
    select var1, var2, covariance=round(covariance,4)
    from t
    union all
    select var2, var1, covariance=round(covariance,4)
    from t
      ) as t
 pivot (max([Covariance]) for [var1] in (' + @cols + ') ) p'
--select @sql
exec(@sql);

rextester demo: http://rextester.com/XTZ92563

qaytaradi:

+------+--------+---------+---------+---------+
| var2 |  ABC   |   DEF   |   MNO   |   XYZ   |
+------+--------+---------+---------+---------+
| ABC  | 0,0021 | 0       | 0,0001  | 0,0005  |
| DEF  | 0      | 0,0016  | 0,0003  | -0,0003 |
| MNO  | 0,0001 | 0,0003  | 0,0012  | -0,0004 |
| XYZ  | 0,0005 | -0,0003 | -0,0004 | 0,0029  |
+------+--------+---------+---------+---------+

yaratilgan so'rov:

select var2, ABC, DEF, MNO, XYZ
  from  (
    select var1, var2, covariance=round(covariance,4)
      from t
    union all
    select var2, var1, covariance=round(covariance,4)
      from t
      ) as t
 pivot (max([Covariance]) for [var1] in (ABC, DEF, MNO, XYZ) ) p
1
qo'shib qo'ydi

Dynamic pivot:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + var1
      from t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')
select  @sql = '
 select var2, ' + @cols + '
  from  (
    select var1, var2, covariance=round(covariance,4)
    from t
    union all
    select var2, var1, covariance=round(covariance,4)
    from t
      ) as t
 pivot (max([Covariance]) for [var1] in (' + @cols + ') ) p'
--select @sql
exec(@sql);

rextester demo: http://rextester.com/XTZ92563

qaytaradi:

+------+--------+---------+---------+---------+
| var2 |  ABC   |   DEF   |   MNO   |   XYZ   |
+------+--------+---------+---------+---------+
| ABC  | 0,0021 | 0       | 0,0001  | 0,0005  |
| DEF  | 0      | 0,0016  | 0,0003  | -0,0003 |
| MNO  | 0,0001 | 0,0003  | 0,0012  | -0,0004 |
| XYZ  | 0,0005 | -0,0003 | -0,0004 | 0,0029  |
+------+--------+---------+---------+---------+

yaratilgan so'rov:

select var2, ABC, DEF, MNO, XYZ
  from  (
    select var1, var2, covariance=round(covariance,4)
      from t
    union all
    select var2, var1, covariance=round(covariance,4)
      from t
      ) as t
 pivot (max([Covariance]) for [var1] in (ABC, DEF, MNO, XYZ) ) p
1
qo'shib qo'ydi

Using PIVOT

Sample Script

Select var1,ABC,DEF,MNO,XYZ from (
Select 
var1,
[1] ABC,
[2] DEF,
[3]MNO,
[4]XYZ 
    from (
select 
Var1 ,
Covariance,
ROW_NUMBER()OVER(PARTITION BY Var2 ORDER BY (select NULL))RN  
from 
    Table1)T
PIVOT (MAX(Covariance) FOR RN IN ([1],[2],[3],[4]))PVT)T
0
qo'shib qo'ydi

Using PIVOT

Sample Script

Select var1,ABC,DEF,MNO,XYZ from (
Select 
var1,
[1] ABC,
[2] DEF,
[3]MNO,
[4]XYZ 
    from (
select 
Var1 ,
Covariance,
ROW_NUMBER()OVER(PARTITION BY Var2 ORDER BY (select NULL))RN  
from 
    Table1)T
PIVOT (MAX(Covariance) FOR RN IN ([1],[2],[3],[4]))PVT)T
0
qo'shib qo'ydi

To'rtta qiymatga ega ekanligingizni bilsangiz, pivot yoki shartli to'plashni ishlatishingiz mumkin:

select max(case when col2 = 'ABC' then covariance else 0 end) as ABC,
       max(case when col2 = 'DEF' then covariance else 0 end) as DEF,
       max(case when col2 = 'MNO' then covariance else 0 end) as MNO,
       max(case when col2 = 'XYZ' then covariance else 0 end) as XYZ
from t
group by col1;

O'zgaruvchan sonli ustunlaringiz bo'lsa, u holda Google "SQL Server dinamik pivot" ni tanlaysiz yoki buni yanada qulayroq dasturda qilishingiz mumkin.

0
qo'shib qo'ydi
rahmat. Men yangi o'zgaruvchilar bo'lishi mumkinligini aytishni unutgan edim. Google dinamik pivo
qo'shib qo'ydi muallif mHelpMe, manba

To'rtta qiymatga ega ekanligingizni bilsangiz, pivot yoki shartli to'plashni ishlatishingiz mumkin:

select max(case when col2 = 'ABC' then covariance else 0 end) as ABC,
       max(case when col2 = 'DEF' then covariance else 0 end) as DEF,
       max(case when col2 = 'MNO' then covariance else 0 end) as MNO,
       max(case when col2 = 'XYZ' then covariance else 0 end) as XYZ
from t
group by col1;

O'zgaruvchan sonli ustunlaringiz bo'lsa, u holda Google "SQL Server dinamik pivot" ni tanlaysiz yoki buni yanada qulayroq dasturda qilishingiz mumkin.

0
qo'shib qo'ydi
rahmat. Men yangi o'zgaruvchilar bo'lishi mumkinligini aytishni unutgan edim. Google dinamik pivo
qo'shib qo'ydi muallif mHelpMe, manba