使用SQL生成类似结果的矩阵 [英] Generating matrix like result using SQL

问题描述

我有一个包含以下字段的表格

RecordId
功能
部门
IsInternal
IsExternal


我有3个部门C01，C02，C03.我想生成一个矩阵，如输出之类的功能，而每个部门作为一个单独的列，如:

I have a table with following fields

RecordId
Functionality
Division
IsInternal
IsExternal


I have 3 division C01, C02, C03. I wanted to generate a matrix like output like functionality and each division as a separate column like :

Functionality  C01IsInternal  C02IsInternal   C03IsInternal
--------------------------------------------------------
F1               ON             OFF          OFF
F2               OFF            NULL         ON
F3               ON             ON           NULL

为实现此目的，我编写了以下查询，它工作正常，但我想知道是否有更好的方法来实现此目的.

To achieve this I have written following query and it is working fine but I wanted to know if there is a better way to achieve this.

select Distinct( fm.Functionality) as Functionality,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C01' and fm1.functionality=fm.functionality) as C01IsInternal,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C02' and fm1.functionality=fm.functionality) as C02IsInternal,
(select IsInternal from FunctionMatrix fm1 where fm1.Division='C03' and fm1.functionality=fm.functionality) as C03IsInternal

from FunctionMatrix fm

目前该表很小，只有40行，但将来可能会增加.

The table is small at the moment with just 40 rows but it may grow in future.

Is there a better way to achieve this result ?

推荐答案

如果您的SQL Server版本为2005或更高版本，则可以考虑使用数据透视运算符.
有关用法的一些信息此处 [

If your SQL-server is version 2005 or later you may consider using the pivot operator.
Some info on the usage here[^].

这篇关于使用SQL生成类似结果的矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！