sql - 如何将两个列表转换为邻接矩阵 SQL Server T-SQL？

Question

我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表，如下所示

Fruit          Colour
Apple          Red
Orange         Red
Berry          Green
PineApple      Green

现在我想把它转换成邻接矩阵查询
如下所示

                Red        Green
Apple           1          0
Orange          1          0
Berry           0          1
PineApple       0          1

如果有水果和颜色，我想要做的是给 1
否则我想给 0

Answer 1

下面是一个例子:

CREATE TABLE #Fruits (name varchar(10), color varchar(10))
GO

INSERT #Fruits VALUES ('Apple', 'Red')
INSERT #Fruits VALUES ('Orange', 'Red')
INSERT #Fruits VALUES ('Berry', 'Green')
INSERT #Fruits VALUES ('PineApple', 'Green')
GO

DECLARE @select_query nvarchar(4000);
DECLARE table_cursor CURSOR
  FOR SELECT DISTINCT color FROM #Fruits;
DECLARE @color varchar(10);

SET @select_query = N'SELECT name'

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @color;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @select_query = @select_query + N', CASE WHEN color = ''' + @color + N''' THEN 1 ELSE 0 END AS ' + @color;
  FETCH NEXT FROM table_cursor INTO @color;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

SET @select_query = @select_query + N' FROM #Fruits';
EXEC sp_executesql @select_query

寻找不同的颜色

构建 SELECT 语句

通过 sp_executesql

执行