is it possible to pass a list of columns instead of manually inserting

Question

Suppose we have a basic select statement, table has multiple columns (more than 300) and need to filter more than 200 records should be in the filtered and we have a view like below now

SELECT * FROM dbo.tablename
UNPIVOT( testcolumn
FOR [unpivotcolumn] IN (
            [columnname1],[columnname2],[columnname3],... ( we have more than 200 here) 
) AS unpivotting

what I tried : I gathered the these column list in a select statement using FOR XML PATH and pasted in the IN statement, but what if I could use this list or another way to put IN statement instead of adding manually ?

any suggestions ? ask me any questions if you need

thanks!

Answer 1

Try the following :

-- Declare the variables
DECLARE @Columns AS NVARCHAR(MAX)
DECLARE @Query AS NVARCHAR(MAX)

-- Get the column names for the table
SELECT @Columns = STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME)
                         FROM INFORMATION_SCHEMA.COLUMNS
                         WHERE TABLE_NAME = 'your_table_name' 
                         --You can add more conditions to filter out specific columns
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- Construct the dynamic SQL statement
SET @Query = '
SELECT * 
FROM dbo.your_table_name
UNPIVOT( testcolumn
FOR [unpivotcolumn] IN (' + @Columns + ')
) AS unpivotting'

-- Execute the dynamic SQL statement
EXEC sp_executesql @Query