使用 SQL Server 2008 R2 的矩阵结果 [英] Matrix result using SQL Server 2008 R2

问题描述

我有一张如下所示的表格:

I have a table as shown below:

示例:

CREATE TABLE matrix
(
  a VARCHAR(10),
  b VARCHAR(10)
);

INSERT INTO matrix VALUES('A','H')
INSERT INTO matrix VALUES('B','I')
INSERT INTO matrix VALUES('C','J')
INSERT INTO matrix VALUES('D','K')
INSERT INTO matrix VALUES('E','L')
INSERT INTO matrix VALUES('F','M')
INSERT INTO matrix VALUES('G','N')
INSERT INTO matrix VALUES('A','M')

SELECT * FROM matrix;

看起来像:

a    b
-------
A    H
B    I
C    J
D    K
E    L
F    M
G    N
A    M

现在我想以以下矩阵格式显示结果:

Now I want to show the result in the following matrix format:

预期结果:

     H    I    J    K    L    M    N
 --------------------------------------
 A | 1    0    0    0    0    1    0
   | 
 B | 0    1    0    0    0    0    0
   |
 C | 0    0    1    0    0    0    0
   |
 D | 0    0    0    1    0    0    0
   |
 E | 0    0    0    0    1    0    0
   |
 F | 0    0    0    0    0    1    0
   |
 G | 0    0    0    0    0    0    1

推荐答案

DECLARE @DynamicPivotQuery AS NVARCHAR(max)   
DECLARE @ColumnName AS NVARCHAR(max)   
DECLARE @ColumnName1 AS NVARCHAR(max) 

SELECT  @ColumnName = Isnull(@ColumnName+',', '') + b,         
        @ColumnName1 = Isnull(@ColumnName1+',', '') + 'isnull(' + b + ',0)' + ' as ' + b  
FROM   (    SELECT DISTINCT b   FROM   matrix)  AS   qry1

--Select @ColumnName,@ColumnName1  
SET @DynamicPivotQuery =      
' Select ' + @ColumnName1 + '    
from  (Select *,1 ''VALUE''  from matrix ) as t pivot  ( Sum(VALUE ) for b in (' +  @ColumnName      + '))   
as pvt ' 

--print @DynamicPivotQuery  
EXEC Sp_executesql  @DynamicPivotQuery 

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