How to get count of Rows automatically when a New Row is added and Rows are to shown as Columns in SQL Server

I am using this query:

SELECT *
FROM tbcount
PIVOT 
    (COUNT(Id) FOR Name IN (F1, F2, F3, F4, F5, F6)) p

and result is

F1 F2 F3 F4 F6 
9  9  8  8  8  

But I have kept F1 TO F6 fixed I want that if a new column is added I will get count automatically without keeping it fixed

Answer

You can try below – using dynamic pivot

declare @sql varchar(max)='',@col_list varchar(8000)=''

set @col_list = (select distinct quotename([Name])+',' from tbcount
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)

set @sql = 'select '+@col_list+' from
tbcount
pivot (count(id) for [Name] in ('+@col_list+'))pv'

exec(@sql)