I have table as below A:Table_Form46_MasterInfo_Log
Sno
DistrictID
HomeTypeId
FieldName
AlertOptionValue
Alert
RatingValue
7
71
6
ChildrenBoth
Yes
ON
1
8
71
8
ChildrenAllowedTelephone
As and when required
ON
1
9
71
8
FrequencyManagementCommitteMeeting
Monthly
ON
2
B: View_Home_Full_InspectionForm(which is a combination of 7 tables
Now my requriement is i want count of alerts that is when alert option value of the fieldname from first table matches that particular column value then count of alert comes
i.e is ChildrenBoth:Yes is alert if this matches in second table ChildrenBoth column then it is alert count of alert will be 1 and soon as per first table alerts .I have worked on this using dynamic query by getting alerts filednames but not able to get
option values
i worked
Declare @lststr varchar(max)
set @lststr=(select FieldName + ',' from Table_Form46_MasterInfo_Log where Alert='ON' for xml path(''))
print @lststr
Not sure about your requirement, it seems that you want to get AlertOptionValue count from the second table and show the count ?
If so , not sure whether it is the best way , but you could try cursor.
Below is my sql.
First my table.
Masterinfo_Log
InfoId FieldName AlertOptionValue
1 ChildrenBoth Yes
2 ChildrenAllowedTelephone As and when required
3 FrequencyManagementCommitteMeeting Monthly
Full_InspectionForm
inspect_id ChildrenBoth ChildrenAllowedTelephone FrequencyManagementCommitteMeeting
1 Yes As and when required Monthly
2 No empty yearly
3 Yes empty yearly
4 Yes As and when required yearly
CREATE TABLE #Temp --declare a template table to store the result
(
fieldName nvarchar(200),
optionValue nvarchar(200),
alertCount int
)
Declare @fieldName nvarchar(200),@optionValue nvarchar(200),@alertCount int, @sql nvarchar(max)
-- declare @fieldname to store fieldname, @optionValue to store alterOptionValue, @alertCount to store alertCount
declare infolog_cursor CURSOR FOR --declare a cursor
Select FieldName , AlertOptionValue
from Masterinfo_Log;
open infolog_cursor --open the cursor
fetch next from infolog_cursor -- fetch the first row and store the value to @fieldName,@optionValue
into @fieldName,@optionValue
while @@FETCH_STATUS = 0 --loop through Masterinfo_Log
begin
set @sql = 'insert into #Temp select @fieldnameInner as fieldname, @optionValueInner as alertOptionValue ,(select count(*) from Full_InspectionForm where '+@fieldName+'=@optionValueInner) as alertCount '
-- insert into the temp table optionValue, fieldname and count of alert using dynamic sql
execute sp_executesql @sql,N'@optionValueInner nvarchar(200),@fieldnameInner nvarchar(200)',@optionValueInner=@optionValue,@fieldnameInner=@fieldName
fetch next from infolog_cursor
into @fieldName,@optionValue
end
close infolog_cursor; --close the cursor
deallocate infolog_cursor
select * from #temp --select the final result
drop table #temp --drop the temp table
The result.
fieldName optionValue alertCount ChildrenBoth Yes 3 ChildrenAllowedTelephone As and when required 2 FrequencyManagementCommitteMeeting Monthly 1
Here I assume all your optionvalue is of type varchar , if not , you should manually convert int to varchar.
Best regards,
Ackerly Xu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
5 Points
77 Posts
Dynamic SQL
Mar 11, 2019 10:03 AM|nagapavanich|LINK
I have table as below A:Table_Form46_MasterInfo_Log
B: View_Home_Full_InspectionForm(which is a combination of 7 tables
Now my requriement is i want count of alerts that is when alert option value of the fieldname from first table matches that particular column value then count of alert comes
i.e is ChildrenBoth:Yes is alert if this matches in second table ChildrenBoth column then it is alert count of alert will be 1 and soon as per first table alerts .I have worked on this using dynamic query by getting alerts filednames but not able to get option values
i worked
Declare @lststr varchar(max)
set @lststr=(select FieldName + ',' from Table_Form46_MasterInfo_Log where Alert='ON' for xml path(''))
print @lststr
declare @query varchar(MAX)='SELECT ' + LEFT(@lststr, LEN(@lststr)-1) + ' FROM View_Home_Full_InspectionForm'
execute(@query)
i get alert coulmns and values but how to compare with main table .Please guide me
Contributor
3500 Points
1300 Posts
Re: Dynamic SQL
Mar 12, 2019 05:24 AM|Ackerly Xu|LINK
Hi nagapavanich,
Not sure about your requirement, it seems that you want to get AlertOptionValue count from the second table and show the count ?
If so , not sure whether it is the best way , but you could try cursor.
Below is my sql.
First my table.
Masterinfo_Log
Full_InspectionForm
CREATE TABLE #Temp --declare a template table to store the result ( fieldName nvarchar(200), optionValue nvarchar(200), alertCount int ) Declare @fieldName nvarchar(200),@optionValue nvarchar(200),@alertCount int, @sql nvarchar(max) -- declare @fieldname to store fieldname, @optionValue to store alterOptionValue, @alertCount to store alertCount declare infolog_cursor CURSOR FOR --declare a cursor Select FieldName , AlertOptionValue from Masterinfo_Log; open infolog_cursor --open the cursor fetch next from infolog_cursor -- fetch the first row and store the value to @fieldName,@optionValue into @fieldName,@optionValue while @@FETCH_STATUS = 0 --loop through Masterinfo_Log begin set @sql = 'insert into #Temp select @fieldnameInner as fieldname, @optionValueInner as alertOptionValue ,(select count(*) from Full_InspectionForm where '+@fieldName+'=@optionValueInner) as alertCount ' -- insert into the temp table optionValue, fieldname and count of alert using dynamic sql execute sp_executesql @sql,N'@optionValueInner nvarchar(200),@fieldnameInner nvarchar(200)',@optionValueInner=@optionValue,@fieldnameInner=@fieldName fetch next from infolog_cursor into @fieldName,@optionValue end close infolog_cursor; --close the cursor deallocate infolog_cursor select * from #temp --select the final result drop table #temp --drop the temp tableThe result.
Here I assume all your optionvalue is of type varchar , if not , you should manually convert int to varchar.
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
5 Points
77 Posts
Re: Dynamic SQL
Mar 12, 2019 05:30 AM|nagapavanich|LINK
Thanks you so much for replay I will check and let you know