locked
Fill Absent text in empty place using pivot sql RRS feed

  • Question

  • User-471420332 posted

    Below is pivot query for my requiremnt.

        Declare @cols NVARCHAR(Max)='';
        ;With log_date AS (
        SELECT BatchStartDate as l_date FROM Table_Batch_Lookup
        UNION ALL
        SELECT DATEADD(dd, 1, l_date)  FROM log_date AS ld , Table_Batch_Lookup AS tb  WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate)
        )
        SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') FROM (SELECT DISTINCT CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM log_date) PV;
    
        Declare @totalScore INT =len(@cols) - len(replace(@cols, ',', ''))
    
        DECLARE @query NVARCHAR(MAX); 
        SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols+'  FROM  Table_TraineeInfo AS t_info  Left JOIN       
                      (SELECT * FROM 
                     (
                         SELECT TraineeID,BatchId,Attendance,CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM Table_Attendance_Log
                     ) x
                     PIVOT 
                     (
                         MAX(Attendance)
                         FOR l_Date IN (' + right(@cols, len(@cols)-1)+ ')
                    ) p ) AS f_pv ON t_info.TraineeID=f_pv.TraineeID JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId
    
                    WHERE t_batch.BatchId=45;
                    ' ;    
        EXEC SP_EXECUTESQL @query;

    Below is my scrip table creation scripts with data. Table_TraineeInfo is candidate registeration details table, Table_Batch_Lookup is batch detail when batch start date and end date base on filling in pivot, Table_Attendance_Log is candidate attendance log.

    CREATE TABLE [dbo].[Table_TraineeInfo](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Name] [varchar](max) NULL,
        [Mobile] [varchar](10) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
    /****** Object:  Table [dbo].[Table_Batch_Lookup]    Script Date: 12/15/2019 04:34:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_Batch_Lookup](
        [BatchId] [int] NULL,
        [BatchStartDate] [datetime] NULL,
        [BatchEndDate] [datetime] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST(0x0000AB25018B80D4 AS DateTime))
    /****** Object:  Table [dbo].[Table_Attendance_Log]    Script Date: 12/15/2019 04:34:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table_Attendance_Log](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Attendance] [varchar](10) NULL,
        [l_date] [datetime] NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))

    My current output

    enter image description here

    I want below output if date is less than or equal to today date fill empty vales to Absent(past date) and Future date should be like that no absent in 12/16/2019 is future date no need to fill absent.

    enter image description here

    Saturday, December 14, 2019 11:23 PM

Answers

  • User-719153870 posted

    Hi mazhar khan,

    I want below output if date is less than or equal to today date fill empty vales to Absent(past date) and Future date should be like that no absent in 12/16/2019 is future date no need to fill absent.

    According to the description and your sql query, i will recommend you use the User-defined Functions of SQL Server to check if the column name date is  less than or equal to today date and its value is null.

    You can refer to below demo, as you can see, i changed little part of your original data since today is 2019-12-16:

    CREATE TABLE [dbo].[Table_TraineeInfo](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Name] [varchar](max) NULL,
        [Mobile] [varchar](10) NULL
    )
    
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
    
    CREATE TABLE [dbo].[Table_Batch_Lookup](
        [BatchId] [int] NULL,
        [BatchStartDate] [datetime] NULL,
        [BatchEndDate] [datetime] NULL
    )
    
    INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST('2019-12-18' AS DateTime))
    
    CREATE TABLE [dbo].[Table_Attendance_Log](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Attendance] [varchar](10) NULL,
        [l_date] [datetime] NULL
    )
    
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST('2019-12-16' AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (247, 45, N'Present', CAST('2019-12-16' AS DateTime))
    
    --select * from [Table_TraineeInfo]
    --select * from [Table_Batch_Lookup]
    --select * from [Table_Attendance_Log]
    
    --Create custom function here IF OBJECT_ID (N'dbo.repNull', N'FN') IS NOT NULL DROP FUNCTION repNull; GO CREATE FUNCTION dbo.repNull(@a nvarchar(30),@d nvarchar(30)) RETURNS nvarchar(30) AS BEGIN DECLARE @ret nvarchar(30); if(CONVERT(NVARCHAR,CONVERT(VARCHAR(10), @d, 111), 106)<=GETDATE() and @a is null) set @ret='Absebt'; else set @ret=@a; return @ret END; GO Declare @cols NVARCHAR(Max)=''; Declare @cols1 NVARCHAR(Max)=''; ;With log_date AS ( SELECT BatchStartDate as l_date FROM Table_Batch_Lookup UNION ALL SELECT DATEADD(dd, 1, l_date) FROM log_date AS ld , Table_Batch_Lookup AS tb WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate) ) SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') ,@cols1 = COALESCE (@cols1 + ',dbo.repNull([' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + '],'''+CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106)+''') as [' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') FROM (SELECT DISTINCT CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM log_date) PV; Declare @totalScore INT =len(@cols) - len(replace(@cols, ',', '')) DECLARE @query NVARCHAR(MAX); SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols1+' FROM Table_TraineeInfo AS t_info Left JOIN (SELECT * FROM ( SELECT TraineeID,BatchId,Attendance as Attendance,CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM Table_Attendance_Log ) x PIVOT ( MAX(Attendance) FOR l_Date IN (' + right(@cols, len(@cols)-1)+ ') ) p ) AS f_pv ON t_info.TraineeID=f_pv.TraineeID JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId WHERE t_batch.BatchId=45; ' ; EXEC SP_EXECUTESQL @query;

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 16, 2019 5:57 AM
  • User77042963 posted
    CREATE TABLE [dbo].[Table_TraineeInfo](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Name] [varchar](max) NULL,
        [Mobile] [varchar](10) NULL
    )  
    GO
     
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
    /****** Object:  Table [dbo].[Table_Batch_Lookup]    Script Date: 12/15/2019 04:34:41 ******/
     
    CREATE TABLE [dbo].[Table_Batch_Lookup](
        [BatchId] [int] NULL,
        [BatchStartDate] [datetime] NULL,
        [BatchEndDate] [datetime] NULL
    ) 
    GO
    INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST(0x0000AB25018B80D4 AS DateTime))
    /****** Object:  Table [dbo].[Table_Attendance_Log]    Script Date: 12/15/2019 04:34:41 ******/
     SET ANSI_PADDING ON
     
    CREATE TABLE [dbo].[Table_Attendance_Log](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Attendance] [varchar](10) NULL,
        [l_date] [datetime] NULL
    )  
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
    
     DECLARE @query NVARCHAR(MAX); 
     declare @ColumnHeaders NVARCHAR(4000) ;
     
    ;With log_date AS (
        SELECT BatchStartDate as l_date FROM Table_Batch_Lookup
        UNION ALL
        SELECT DATEADD(dd, 1, l_date)  FROM log_date AS ld , Table_Batch_Lookup AS tb  WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate)
        )
    
    Select @ColumnHeaders = STUFF( (SELECT ',' + Case when l_date<Cast(getdate() as date) then 'ISNULL' else '' end + ' (MAX(Case when format(l_date,''yyyy-MM-dd'')='+ quotename(format(l_date,'yyyy-MM-dd'),'''') +' then  [Attendance]  else null end)'
    +Case when l_date<Cast(getdate() as date) then ', ''Absent'')' else ')' end + ' as ' + Quotename(format(l_date,'yyyy-MM-dd'),'[')  + char(10)+char(13)
    FROM  log_date
    Order by l_date  
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
        
     --print @ColumnHeaders
     
     SET @query ='
     SELECT t_info.TraineeID,t_batch.BatchId
     ,t_info.Name,t_info.Mobile, '+@ColumnHeaders +' 
     FROM  Table_TraineeInfo AS t_info  Left JOIN  Table_Attendance_Log  
     ON t_info.TraineeID=Table_Attendance_Log.TraineeID
     JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId
     WHERE t_batch.BatchId=45
     Group by t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile  ;
     '
     
     --print @query
     EXEC SP_EXECUTESQL @query;
    
    
    
    
    drop TABLE [dbo].[Table_Attendance_Log]
    ,  [dbo].[Table_Batch_Lookup]
    ,[dbo].[Table_TraineeInfo]
    TraineeID	BatchId	Name	Mobile	2019-12-13	2019-12-14	2019-12-15	2019-12-16
    243	45	demo201	9888562341	Present	Present	Present	NULL
    244	45	demo202	9888562342	Absent	Absent	Present	NULL
    246	45	demo204	9888562344	Absent	Present	Present	NULL
    247	45	demo205	9999999999	Absent	Absent	Absent	NULL



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 16, 2019 3:42 PM

All replies

  • User-719153870 posted

    Hi mazhar khan,

    I want below output if date is less than or equal to today date fill empty vales to Absent(past date) and Future date should be like that no absent in 12/16/2019 is future date no need to fill absent.

    According to the description and your sql query, i will recommend you use the User-defined Functions of SQL Server to check if the column name date is  less than or equal to today date and its value is null.

    You can refer to below demo, as you can see, i changed little part of your original data since today is 2019-12-16:

    CREATE TABLE [dbo].[Table_TraineeInfo](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Name] [varchar](max) NULL,
        [Mobile] [varchar](10) NULL
    )
    
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
    
    CREATE TABLE [dbo].[Table_Batch_Lookup](
        [BatchId] [int] NULL,
        [BatchStartDate] [datetime] NULL,
        [BatchEndDate] [datetime] NULL
    )
    
    INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST('2019-12-18' AS DateTime))
    
    CREATE TABLE [dbo].[Table_Attendance_Log](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Attendance] [varchar](10) NULL,
        [l_date] [datetime] NULL
    )
    
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST('2019-12-16' AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (247, 45, N'Present', CAST('2019-12-16' AS DateTime))
    
    --select * from [Table_TraineeInfo]
    --select * from [Table_Batch_Lookup]
    --select * from [Table_Attendance_Log]
    
    --Create custom function here IF OBJECT_ID (N'dbo.repNull', N'FN') IS NOT NULL DROP FUNCTION repNull; GO CREATE FUNCTION dbo.repNull(@a nvarchar(30),@d nvarchar(30)) RETURNS nvarchar(30) AS BEGIN DECLARE @ret nvarchar(30); if(CONVERT(NVARCHAR,CONVERT(VARCHAR(10), @d, 111), 106)<=GETDATE() and @a is null) set @ret='Absebt'; else set @ret=@a; return @ret END; GO Declare @cols NVARCHAR(Max)=''; Declare @cols1 NVARCHAR(Max)=''; ;With log_date AS ( SELECT BatchStartDate as l_date FROM Table_Batch_Lookup UNION ALL SELECT DATEADD(dd, 1, l_date) FROM log_date AS ld , Table_Batch_Lookup AS tb WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate) ) SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') ,@cols1 = COALESCE (@cols1 + ',dbo.repNull([' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + '],'''+CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106)+''') as [' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106) + ']','[' + CONVERT(NVARCHAR, l_Date, 106) + ']') FROM (SELECT DISTINCT CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM log_date) PV; Declare @totalScore INT =len(@cols) - len(replace(@cols, ',', '')) DECLARE @query NVARCHAR(MAX); SET @query = 'SELECT t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile'+@cols1+' FROM Table_TraineeInfo AS t_info Left JOIN (SELECT * FROM ( SELECT TraineeID,BatchId,Attendance as Attendance,CONVERT(VARCHAR(10), l_Date, 111) AS l_date FROM Table_Attendance_Log ) x PIVOT ( MAX(Attendance) FOR l_Date IN (' + right(@cols, len(@cols)-1)+ ') ) p ) AS f_pv ON t_info.TraineeID=f_pv.TraineeID JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId WHERE t_batch.BatchId=45; ' ; EXEC SP_EXECUTESQL @query;

    Below is the result of this demo:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 16, 2019 5:57 AM
  • User77042963 posted
    CREATE TABLE [dbo].[Table_TraineeInfo](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Name] [varchar](max) NULL,
        [Mobile] [varchar](10) NULL
    )  
    GO
     
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (243, 45, N'demo201', N'9888562341')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (244, 45, N'demo202', N'9888562342')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (246, 45, N'demo204', N'9888562344')
    INSERT [dbo].[Table_TraineeInfo] ([TraineeID], [BatchId], [Name], [Mobile]) VALUES (247, 45, N'demo205', N'9999999999')
    /****** Object:  Table [dbo].[Table_Batch_Lookup]    Script Date: 12/15/2019 04:34:41 ******/
     
    CREATE TABLE [dbo].[Table_Batch_Lookup](
        [BatchId] [int] NULL,
        [BatchStartDate] [datetime] NULL,
        [BatchEndDate] [datetime] NULL
    ) 
    GO
    INSERT [dbo].[Table_Batch_Lookup] ([BatchId], [BatchStartDate], [BatchEndDate]) VALUES (45, CAST(0x0000AB2200000000 AS DateTime), CAST(0x0000AB25018B80D4 AS DateTime))
    /****** Object:  Table [dbo].[Table_Attendance_Log]    Script Date: 12/15/2019 04:34:41 ******/
     SET ANSI_PADDING ON
     
    CREATE TABLE [dbo].[Table_Attendance_Log](
        [TraineeID] [int] NULL,
        [BatchId] [int] NULL,
        [Attendance] [varchar](10) NULL,
        [l_date] [datetime] NULL
    )  
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB220127842A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime))
    INSERT [dbo].[Table_Attendance_Log] ([TraineeID], [BatchId], [Attendance], [l_date]) VALUES (246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
    
     DECLARE @query NVARCHAR(MAX); 
     declare @ColumnHeaders NVARCHAR(4000) ;
     
    ;With log_date AS (
        SELECT BatchStartDate as l_date FROM Table_Batch_Lookup
        UNION ALL
        SELECT DATEADD(dd, 1, l_date)  FROM log_date AS ld , Table_Batch_Lookup AS tb  WHERE ld.l_date<DATEADD(dd, -1, tb.BatchEndDate)
        )
    
    Select @ColumnHeaders = STUFF( (SELECT ',' + Case when l_date<Cast(getdate() as date) then 'ISNULL' else '' end + ' (MAX(Case when format(l_date,''yyyy-MM-dd'')='+ quotename(format(l_date,'yyyy-MM-dd'),'''') +' then  [Attendance]  else null end)'
    +Case when l_date<Cast(getdate() as date) then ', ''Absent'')' else ')' end + ' as ' + Quotename(format(l_date,'yyyy-MM-dd'),'[')  + char(10)+char(13)
    FROM  log_date
    Order by l_date  
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
        
     --print @ColumnHeaders
     
     SET @query ='
     SELECT t_info.TraineeID,t_batch.BatchId
     ,t_info.Name,t_info.Mobile, '+@ColumnHeaders +' 
     FROM  Table_TraineeInfo AS t_info  Left JOIN  Table_Attendance_Log  
     ON t_info.TraineeID=Table_Attendance_Log.TraineeID
     JOIN Table_Batch_Lookup as t_batch ON t_batch.BatchId=t_info.BatchId
     WHERE t_batch.BatchId=45
     Group by t_info.TraineeID,t_batch.BatchId,t_info.Name,t_info.Mobile  ;
     '
     
     --print @query
     EXEC SP_EXECUTESQL @query;
    
    
    
    
    drop TABLE [dbo].[Table_Attendance_Log]
    ,  [dbo].[Table_Batch_Lookup]
    ,[dbo].[Table_TraineeInfo]
    TraineeID	BatchId	Name	Mobile	2019-12-13	2019-12-14	2019-12-15	2019-12-16
    243	45	demo201	9888562341	Present	Present	Present	NULL
    244	45	demo202	9888562342	Absent	Absent	Present	NULL
    246	45	demo204	9888562344	Absent	Present	Present	NULL
    247	45	demo205	9999999999	Absent	Absent	Absent	NULL



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 16, 2019 3:42 PM