Sql server:return column names based on record values

Even if possible, modifying the column names based on the values on the data is not typically done. This would make the coding on the calling side much harder since the program doesn't know the names anymore so referencing the columns in the result set becomes harder, in some cases even impossible.

Another thing is that what if the result set contains two rows and the values for the same column are different on these rows? What should the column name be in such case?

[Edit:]
One way to do this would be to fetch the data and based on that create the query with custom column names.

But in order to do that you would need to fetch the data one time for deciding the column names and then fetch the data again to have it with custom column names. As pointed out this wouldn't make sense. It would be much easier for example to fetch the data to the client and then modify the display names as needed...

To demonstrate one variation how this could be achieved consider the following. But as said, I really don't recommend this.

Create the test data

CREATE TABLE ColumNameTest (
col1 int,
col2 int);

INSERT INTO ColumNameTest (col1, col2) VALUES
(1,2),
(3,null),
(null,6);

Create and run the query

DECLARE @query nvarchar(max)
DECLARE @alias nvarchar(max)
SET @query = ' SELECT '

SELECT @alias = STUFF((SELECT ',' + COALESCE(CAST(col1 AS nvarchar(100)), 'null') FROM columnametest FOR XML PATH('')),1,1,'');
SET @query = @query + ' col1 AS [' + @alias + ']'

SELECT @alias = STUFF((SELECT ',' + COALESCE(CAST(col2 AS nvarchar(100)), 'null') FROM columnametest FOR XML PATH('')),1,1,'');
SET @query = @query + ', col2 AS [' + @alias + ']'

SET @query = @query + ' FROM ColumNameTest'
PRINT @query
EXEC sp_executesql @query

The dynamically built query would look like

SELECT  col1 AS [1,3,null], col2 AS [2,null,6] FROM ColumNameTest

and the result is

1,3,null   2,null,6
--------   --------
1          2
3          NULL
NULL       6