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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With