How to convert two lists into adjacency matrix SQL Server T-SQL?

Question

I have table in SQL Server 2005 that contains two columns Fruit and Colour as shown below

Fruit          Colour
Apple          Red
Orange         Red
Berry          Green
PineApple      Green

Now i want to convert it into an adjacency matrix query as shown below

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

what i want to do is give 1 if Fruit and Colour are there else i want to give a 0

Answer 1

Here is an example:

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

1. Find distinct colors
2. Build SELECT statment
3. Execute it via sp_executesql