Try this...
Declare @ColumnName Nvarchar(40) , @Sql Nvarchar(1000),@ColumnCount Int, @I Int
Select @Sql='Select Id,Day1,Day2,Day3, ',@I=0,@ColumnCount=Count(COLUMN_NAME)
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='Temp' and COLUMN_NAME Like 'Day%'
While @I<@ColumnCount
Begin
Select @ColumnName=COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='Temp'
Order by COLUMN_NAME
OFFSET @I Rows
Fetch Next 1 Rows only;
Set @Sql= @Sql +' ' +'Case When '+ @ColumnName +' =''P'' Then 1 Else 0 End +'
Set @I=@I+1
End
Set @Sql = Left(@Sql,len(@Sql)-1) +' as NoofDaysPresent From Temp'
Exec(@Sql)
Replace
Temp
with your
TableName
Tested:
Create Table Temp (Id Int, Day1 varchar(2),Day2 varchar(2),Day3 varchar(2))
Insert into Temp
Select 1,'P','P','P' Union all
Select 2,'P','A','P' Union all
Select 3,'P','P','P' Union all
Select 4,'P','P','A' Union all
Select 5,'P','P','P'
Output:
Id Day1 Day2 Day3 NoofDaysPresent
1 P P P 3
2 P A P 2
3 P P P 3
4 P P A 2
5 P P P 3
If You Want to type every thing then u can use...
Select Id,ColumnNames,Case When Day1='P' Then 1 Else 0 End+..... as NoofDaysPresent From TableName