locked
Dynamic SQL RRS feed

  • Question

  • User1804579801 posted

    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

    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 

    SanctionCapacity SixYears ElevenYears FifteenYears EighteenYears AboveEighteenYears NoofChildreninHome ChildrenBoth Bathing NewAdmissions ChildrenReleased ChildrenReferredByCWC ChildrenProducedByCWC NoofChildrenPrevMonth SpecialNeedsDetails Intervention IndividualCare BusinessType BusinessTypeOthers Rented CCTVcameras SecurityAdequate SufficientSpace NoofSickRooms SickRoomsDetails NoofCounselingRooms CouncelingRoomsDetails SportsFacilities AccessRecreational TVset GamesIndoor GamesOutdoor AccessoriestoPlay Picnics Interaction Recreationroom AllowedTV IndoorGames IndoorGamesOthers CookingArea IndividualThalis CookingUtensils OvenAvaliable Gasstove GasCylinder Chimney AdequateWaterSupply AdequateDrinkingWater Cooking NoofBathrooms Flush TapsFunctioning FloorSlippery Drains Fittings Cob DoorLatch DoorPeep AdequatelyWater AdequateBuckets Personaltoiletries WashingPowder ChildWashClothes WasherMan WashingMachine FrequencyBath AvailableOpenSpace AvaliableClassRooms AvailableSpaceVT Indoors CleaningExercise Facilities DoorsMaintained RoomsDormitories AlternateProvision Outdoors Sweeping ClothesProvided Mattresses Pillows MattressClean SeparateCupboards Bedsheets Blankets PersonalLockers ChangingUndergarments ChangingUndergarmentsOthers NoofSets NewClothes Stitched Same OtherArticles Medicalfacilities Nutrition SafeDrinkingWater DailyMorning DayTime Afternoon Evening LateEveningNight Education ComputerWithInternet UserCredntials FacilityFunctional ChildrenAllowedFacility TelephoneforOfficialPurposes ChildLineDisplayed ChildrenAllowedTelephone Counseling VocationalTraining RecreationFacilities Linkagesdeveloped ImplementationTrack OtherProgrammes FormationChildrenscommitee Agebelow7 Age7to11 Age12to15 Age16to18 FrequencyChildrenCommitteMeeting FormationManagementCommittee ConstitutionManagementDate FrequencyManagementCommitteMeeting StaffAttendanceRegister ChildrenAttendanceRegister CentralAmissionRegister IndividualCaseFileWithCarePlan CommunicationwithCWCJJB ChildrensSuggestionBook ChildrensSuggestionBox MedicalFileCards PersonalBelongingsRegister ManagementCommitteeMinutesRegister ChildrensCommitteeMinutesRegister NutritionDietFiled OtherRecordMaintained ObservationsRemarks DistrictId NGOId HomeTypeId FormStatus
    22 22 23 22 22 22 32 Yes Yes 22 22 22 22 22 ffffffffffffffffff ffffffffffffffff Yes Non–Combustible Buildings  Owned No No No 444 fffffffffffffffffff 1 eeeeeeeeeeeee Available Yes Yes Yes Yes Yes Yes Yes Yes Morning,Afternoon Hide and Seek,Ludo,Chess Yes Yes Yes Yes Yes Yes Yes Yes Yes Machine 12 Yes Yes Yes Yes Yes Yes No No No No No No No No No Once No No No No No No No No No No Once No No No No No No No No Quarterly One Monthly Stitched Same hgdajfh WE WE WE WE WE WE WE WE WE Yes Yes Yes Yes Yes Yes Fixed Timing WE WE WE WE WE WE No 122 1112 112 112 Fortnightly No 00:00.0 Fortnightly No No No No No No No No No No No No WE WE 71 1 1 DIC-Completed
    1 1 1 1 1 1 1 No No 1 1 1 1 1 1 1 No Fire resistive Buildings Owned No No No 1 1 1 1 Not Available No No No No No No No No Morning Chess No No No No No No No No No Cook 1 No No No No No No No No No No No No No No No Once No No No No No No No No No No Once No No No No No No No No Monthly One Monthly Bought Same 1 1 1 1 1 1 1 1 1 1 No No No No No No As and when required 1 1 1 1 1 1 Yes 1 1 1 1 Fortnightly Yes 00:00.0 Weekly Yes No No No No No No No No No No No 1 1 71 1 1 DIC-Completed
    1 1 1 1 1 11 1 No No 1 1 1 1 1 1 f 1 No Wood framed Buildings Rented No No No 1 sdfsdf 1 fsdfdfds Not Available No No No No No No No No Morning,Afternoon Hide and Seek,Ludo Yes No No No No No No No No Machine 12 No Yes No No No No No No No No No No No No No Once No No No No No No No No No No Once No No No No No No No No Monthly One Monthly Bought Same sdsdfsdfsdf hfsjhjk\h dfjshjkl fdshjk dgfhyukilod dsfgthyuisdfl hgdswejuhasd sduyaufhio; fdshyusdhy sadsa Yes Yes Yes Yes Yes Yes Fixed Timing ghghggjk gfgfhjgfhjgfhj gfthjyfgthygfhj yuu ghghghfjgfhj gfffhjgfhjgfhjgfhj Yes 12 111 11 11 Weekly Yes 00:00.0 Weekly Yes No No No No No No No No No No No GJFH\SAGK FDHFDKHJK 71 1 1 DIC-Completed
    1 1 1 1 1 1 1 No No 1 1 1 10 1 dfasdf1 fsdfdsfsdf sdfsdfsd dfasdfas dfsdafasdfasd fasdfasd fasdf1 dfasdfasdf No Others dd Owned No No No 22 sdfdsf 22 ddfs Not Available No No No No No No No No Yes No No No No No No No No Machine 1111 No No No No No No No No No No No No No No No More than Once Yes No No No No No No No No No Twice in a day No No No No No No No No Monthly One Monthly Stitched Same dfasdf asdfasdf asdfsdfasd fasd fasdfas df fvasdf asdfsdfasdfsd fd sdfasd fasdf asdfasdf  1 2 3 4 5 6 7 8 9 Yes No No Yes No Yes Fixed Timing 10 fasdf fdfsasdf fdasd 11  dfsdf sdf sdfas dfsddsf asdfsdf  12 13 14 15 No 1 1 1 1 Weekly Yes 00:00.0 Weekly Yes No No No No No No No No No No Yes dfasdfasd fasdfasdfsad sdgsdfg gdfsgsdfg dfasdf asdf asdfasd fasdfasdfs dfgsdfgfd 71 1 1 DIC-Completed
    Monday, March 11, 2019 10:03 AM

All replies

  • User-893317190 posted

    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

    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

    Tuesday, March 12, 2019 5:24 AM
  • User1804579801 posted

    Thanks you so much for replay I will check and let you know

    Tuesday, March 12, 2019 5:30 AM