Pivot query result (column name will change)

Question

Hi there,

Have been working on a huge database task and one part of it is the below.

How do i pivot this 1 row with dynamic columns (column names will change)

Query:

select top 1 * from INFORMATION_SCHEMA.columns

Output should be

ColumnName | Value

Thanks.

Answer 1

To pivot a single row in SQL Server while dynamically changing the column names, you can use the UNPIVOT operator or a combination of UNION ALL. However, since SQL Server doesn't directly support dynamic column names in the pivoting process, you'll need to build a dynamic SQL query.

Here's a general approach to achieve this:

1. First, you can query the INFORMATION_SCHEMA.COLUMNS to get the column names.
2. Next, build a dynamic SQL statement that constructs the pivot query.
3. Finally, execute the dynamic SQL.

DECLARE @columns NVARCHAR(MAX);

DECLARE @sql NVARCHAR(MAX);

-- Step 1: Get the column names

SELECT @columns = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'YourTableName'; -- Replace with your table name

-- Step 2: Construct the dynamic SQL for pivoting

SET @sql = N'SELECT COLUMN_NAME, VALUE

FROM (SELECT TOP 1 * FROM YourTableName) AS SourceTable

UNPIVOT (VALUE FOR COLUMN_NAME IN (' + @columns + ')) AS UnpivotedTable;';

-- Step 3: Execute the dynamic SQL

EXEC sp_executesql @sql;