Can SQL Server Pivot without knowing the resulting column names?

The example you linked to uses dynamic SQL. Unfortunately, there is no other built-in method for pivoting in SQL Server when the output columns are not known in advance.

If the data is not too large, it's probably easiest to simply run a normal row query from ASP.NET and perform your pivot in the application code. If the data is very large, then you'll have to generate the SQL dynamically after first querying for the possible column values.

Note that you don't actually need to write a SQL statement that generates dynamic SQL; you can simply generating the SQL in ASP.NET, and that will most likely be much easier. Just don't forget to escape the distinct Site values before chucking them in a generated query, and don't forget to parameterize whatever parts of the SQL statement that you normally would without the pivot.

It's been more than 10 years, and the same problem came to me.

Is there any way to pivot without knowing column names?

Then I searched something and found the below solution. We can achieve this by using dynamic query. I am adding this so it will help someone.

CREATE TABLE TEMP 
(
     [Month] varchar(50),
     [Site] varchar(50),
     Val int
)

INSERT INTO TEMP
VALUES ('2009-12', 'Microsoft', 10),
       ('2009-11', 'Microsoft', 12),
       ('2009-10', 'Microsoft', 15),
       ('2009-12', 'Google', 20),
       ('2009-11', 'Google', 8),
       ('2009-10', 'Google', 11),
       ('2009-12', 'Facebook', 13),
       ('2009-11', 'Facebook', 12),
       ('2009-10', 'Facebook', 5)

DECLARE @Columns as VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME([Site])
FROM
    (SELECT DISTINCT [Site] FROM TEMP) AS B
ORDER BY B.[Site]

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT Month, ' + @Columns + ' 
FROM
(
 select Month,[Site],Val from TEMP
) as PivotData
PIVOT
(
   Sum(Val)
   FOR [Site] IN (' + @Columns + ')
) AS PivotResult
ORDER BY Month'


EXEC(@SQL);

As you can see I took the column values into a string and then dynamically use that to pivot.

Here is the result: