sql server - How to pivot table without typing column name manually in SQL

Question

I was trying to use pivot for 30 columns, I don't want to do it by typing manually, could anyone help me on that. For example, I have some data like this

1   10000   may
1   400     july
2   4500    Jan
2   35000   Apri
1   5000    Feb
1   3000    Sep
2   200     Feb
2   90500   Oct
1   6000    Mar
1   5000    Mar
2   2500    Nov

My desired result would be like this.

empid  Apri   Feb   Jan   july  Mar     may     Nov     Oct     Sep
1      NULL   5000  NULL  400   6000    10000   NULL    NULL    3000
2      3500   200   4500  NULL  NULL    NULL    2500    90500   NULL

appreciate if you could give a hint.

question from:https://stackoverflow.com/questions/65942126/how-to-pivot-table-without-typing-column-name-manually-in-sql

Answer 1

You could transfer all distinct month into XML, then use it in pivot

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.month)
            FROM #monthly_sales c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,' ')

set @query = 'SELECT empid, ' + @cols + ' from 
            (
                select empid
                    , amount
                    , month
                from #Monthly_sales
           ) x
            pivot 
            (
                 max(amount)
                for month in (' + @cols + ')
            ) p '
EXEC (@query)