issue with dynamic query

Question

I am working with windows form(Infragistics grid) with ms sql what is my task : I want to store event related data in database see my below data strucuture: this is event_master -> master table

SELECT *
FROM dbo.EVENT_MASTER
WHERE EVENT_ACTIVE = 1

EVENT_ID    EVENT_GROUP EVENT_NAME              EVENT_ORDER SELF    COUPLE   FAMILY ALL_FAMILY  EVENT_ACTIVE
15           Cricket    Cricket Lunch (10-Feb)  1            1          1       0       0           1               
16           Cricket    Cricket Dinner (10-Feb) 2            0          0       1       0           1
17           Cricket    Cricket Lunch (11-Feb)  1            0          0       0       1           1
21           Garba      Cricket lunch (13-Feb)  3            1          0       0       0           1

below is event_member_master table - >   master table

SELECT *
FROM dbo.EVENT_MEMBER_MASTER

EVENT_MEMBER_ID     EVENT_MEMBER_NAME
    1                   Self
    2                   Wife_Husband
    3                   Father
    4                   Mother
    5                   Brother1
    7                   Brother2
    8                   Brother3
    9                   Brother4
    10                  Sister1
    11                  Sister2
    12                  Sister3
    13                  Sister4
    22                  Child1
    23                  Child2
    24                  Child3
    25                  Child4

Here I am storing the employ event data(means employee is present in event or not) Event_Entry table

SELECT *
FROM dbo.EVENT_ENTRY
ORDER BY EMPLOYEE_NO, EVENT_ID,EVENT_MEMBER_ID

EMPLOYEE_NO EVENT_ID    EVENT_MEMBER_ID IS_PRESENT
14           15             1               1
14           15             2               1
14           16             1               1
14           16             2               0
14           16             22              1
14           16             23              1
53           15             1               1
53           15             2               1
53           16             1               1
53           16             2               1
53           16             22              1
53           16             23              0

Below is my sql query :

    DROP TABLE IF EXISTS #TB,##tmp
    
    SELECT EVENT_ID,
           CASE WHEN ALL_FAMILY = 1 THEN 'ALL_FAMILY' WHEN FAMILY = 1 THEN 'FAMILY' WHEN COUPLE = 1 THEN 'COUPLE' ELSE 'SELF' END TYPE,
           EVENT_NAME
    INTO #TB
    FROM EVENT_MASTER WITH(NOLOCK) 
    WHERE EVENT_ACTIVE = 1

    SELECT *
    FROM #TB

    DECLARE @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX)
        
    select @cols = STUFF((SELECT ',' + QUOTENAME(EVENT_ID) 
                        from #TB
                        group by EVENT_ID, TYPE
                        order by EVENT_ID
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    --SELECT @cols
    
    set @query = N'SELECT ' + @cols + N'
                 into ##tmp
                 from 
                 (
                    select TYPE, EVENT_ID
                    from #TB
                ) x
                pivot 
                (   
                    min(TYPE)
                    for EVENT_ID in (' + @cols + N')
                ) p '
    
    exec sp_executesql @query;

    SELECT  
                       sub.EMPLOYEE_NO,
                       Sub.Item,
                       Sub.Value,
                       Sub.Gender,
                       T.* 
                       --,T.myVal1
                       --,T.myVal2
                       --,T.myVal3
                       --,t.myVal4
                FROM (                  
                    SELECT
                       A.EMPLOYEE_NO,
                       Item  = B.[Key]
                      ,Value = B.[Value],
                      CASE WHEN B.[Key] = 'EMPLOYEE_FIRST_NAME'  THEN A.GENDER
                           WHEN B.[Key] = 'FATHER_NAME'  THEN 'Male'
                           WHEN B.[Key] = 'MOTHER_NAME'  THEN 'Female'
                           WHEN B.[Key] = 'BROTHER_NAME1'  THEN 'Male'
                           WHEN B.[Key] = 'BROTHER_NAME2'  THEN 'Male'
                           WHEN B.[Key] = 'BROTHER_NAME3'  THEN 'Male'
                           WHEN B.[Key] = 'BROTHER_NAME4'  THEN 'Male'
                           --WHEN B.[Key] = 'SISTER_NAME1'  THEN 'Female' 
                           --WHEN B.[Key] = 'SISTER_NAME2'  THEN 'Female'
                           --WHEN B.[Key] = 'SISTER_NAME3'  THEN 'Female'
                           --WHEN B.[Key] = 'SISTER_NAME4'  THEN 'Female'
                           WHEN B.[Key] = 'LIFE_PARTNER_NAME'  THEN 'Female'
                           WHEN B.[Key] = 'CHILD_NAME1'  THEN A.CHILD_GENDER1 
                           WHEN B.[Key] = 'CHILD_NAME2'  THEN A.CHILD_GENDER2
                           WHEN B.[Key] = 'CHILD_NAME3'  THEN A.CHILD_GENDER3
                           WHEN B.[Key] = 'CHILD_NAME4'  THEN A.CHILD_GENDER4 ELSE 'Female'END Gender

                 From  dbo.EMPLOYEE_DETAILS A
                 Cross Apply (  SELECT * 
                                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                                Where [Key] not in ('ID','Other','Columns','ToExclude') AND [Key] IN (
                                'EMPLOYEE_FIRST_NAME','FATHER_NAME','MOTHER_NAME',
                                'BROTHER_NAME1','BROTHER_NAME2','BROTHER_NAME3','BROTHER_NAME4',
                                'SISTER_NAME1','SISTER_NAME2','SISTER_NAME3','SISTER_NAME4','LIFE_PARTNER_NAME',
                                'CHILD_NAME1','CHILD_NAME2','CHILD_NAME3','CHILD_NAME4')
                             ) B
                    )AS Sub
                    CROSS JOIN ##tmp AS T
                    WHERE Sub.EMPLOYEE_NO = @EMPLOYEE_NO
        
    DROP TABLE IF EXISTS #TB,##tmp

Current output what I want event id is dynamically generate

• 15 eventid is self and couple type then I want to active only EMPLOYEE_FIRST_NAME and LIFE_PARTNER_NAME checkbox active in infragistic grid
• 16 eventid is family type then I want to active only EMPLOYEE_FIRST_NAME and LIFE_PARTNER_NAME and ,CHILD_NAME1,CHILD_NAME2,CHILD_NAME3,CHILD_NAME4 checkbox active in infragistic grid
• 17 eventid is all family type then I want to active all checkbox active in infragistic grid
• 21 eventid is self type then I want to active only EMPLOYEE_FIRST_NAME checkbox active in infragistic grid   --------------
  • employee no 14 have only 15 and 16 event_id data so I want to fetch that data in grid but same employee no 14 have no data of  17 and 21 event_id so I want to store 17 and 21 event_id data of employee no 14.   if 14 employee have already event_id data then fetch that data in grid but if 14 no employee have no data of event id then I want to store that event_id data(means fetching the data if data already available and storing the data)   please help