If I use #Final as a table (instead of #3Final) I get a message Invalid Object Name #Final. Is there a way not to use a Global Temp table (assuming I want to use a temp table?) Thanks.
IF OBJECT_ID('tempdb..#Distinct') > 0 DROP TABLE #Distinct
CREATE TABLE #Distinct
(Animal varchar(99), Color varchar(99))
INSERT INTO #Distinct
SELECT 'Dog', 'White' UNION ALL
SELECT 'Dog', 'Blue' UNION ALL
SELECT 'Dog', 'Black' UNION ALL
SELECT 'Dog', 'Purple' UNION ALL
SELECT 'Dog', 'Orange' UNION ALL
SELECT 'Horse', 'Brown' UNION ALL
SELECT 'Cat', 'White' UNION ALL
SELECT 'Cat', 'Blue' UNION ALL
SELECT 'Cat', 'Purple'
IF OBJECT_ID('tempdb..##Final') > 0 DROP TABLE ##Final
DECLARE @SQLStatement nVARCHAR(MAX) = 'SELECT Animal'
SELECT @SQLStatement = @SQLStatement + ', ' + CHAR(13) + '['+ Color + '] = MAX(CASE WHEN Color = ''' + Color + ''' THEN ''Y'' ELSE ''N'' END) '
FROM #Distinct A
GROUP BY Color ORDER BY Color
SELECT @SQLStatement = @SQLStatement + CHAR(13) + 'INTO ##Final FROM #Distinct GROUP BY Animal'
EXEC (@SQLStatement)
--PRINT (@SQLStatement)
SELECT * FROM ##Final