sql查询以特定格式选择记录(使用pivot或其他)

问题描述

我有一个这样的 SQL 表:

I have an SQL table like this:

Animal1       Animal2       Corelation
---------+---------------+--------------
Cat             Cat           1       
Cat             Dog           0.6     
Cat             Mouse         0.8     
Dog             Cat           0.6 
Dog             Dog           1    
Dog             Mouse         0.4      
Mouse           Cat           0.8     
Mouse           Dog           0.4     
Mouse           Mouse         1   

我正在寻找返回以下结果的 SQL 查询:

---

I'm looking for an SQL query to return the following results:

Animal 1       Cat              Dog               Mouse
---------+---------------+------------------+---------------+
Cat            1                  0.6               0.8
Dog            6                  1                 0.4
Mouse          0.8                0.4               1

基本上我想要表格的可读性更强.

Basically i want more readable version of the table.

我尝试像这样使用枢轴:

I tried to use pivot like this:

use SymbolsDB
select * from [AnimalsTable]
pivot (
   [Corelation]
   for [Animal2] in (select * from [Animal2]
)

但是它不起作用.我不确定我是否了解支点的工作原理以及是否可以在我的情况下使用它.或者有其他方法可以做到吗?(我试图避免循环，因为我有 100 万条记录)

But it is not working. i am not sure if I understand how the pivot works and if it can be used in my case. Or is there another way to do it? (I am trying to avoid loops because I have 1 million record)

谢谢

推荐答案

您不能在 PIVOT 中放置 SELECT 语句来返回值列表，则必须保持不变.如果你正在寻找一个动态的 PIVOT 那么你将需要使用这样的东西:

You cannot place a SELECT statement inside of the PIVOT to return the list of values, then must be constant. If you are looking for a dynamic PIVOT then you will need to use something like this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Animal2) 
                    from animals
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Animal1, ' + @cols + ' from 
             (
                select animal1, animal2, Corelation
                from animals
            ) x
            pivot 
            (
                min(Corelation)
                for animal2 in (' + @cols + ')
            ) p '

execute(@query)

参见SQL Fiddle with demo

这篇关于sql查询以特定格式选择记录(使用pivot或其他)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！