Below is pivot query for my requirement.
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 script for table creation scripts with data. Table_TraineeInfo is candidate registration 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'),
(244, 45, N'demo202', N'9888562342'),
(246, 45, N'demo204', N'9888562344'),
(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)),
(243, 45, N'Present', CAST(0x0000AB2301281C2A AS DateTime)),
(243, 45, N'Present', CAST(0x0000AB240128E416 AS DateTime)),
(244, 45, N'Present', CAST(0x0000AB24012A05AB AS DateTime)),
(246, 45, N'Present', CAST(0x0000AB23012B245A AS DateTime)),
(246, 45, N'Present', CAST(0x0000AB24012B245A AS DateTime))
My current output
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.
Advertisement
Answer
In a regular (ie non-dynamic) pivot query, you typically use COALESCE() to assign a default value when an aggregate function yields null.
This is a bit more complicated with your dynamic SQL. Basically you need to set a second variable to hold the COALESCE() expressions that you want to put in the SELECT clause. I called it @select_cols; we use COALESCE() and a CASE expression that checks if the date in the future before assigning the default.
Here is your updated code:
Declare @cols NVARCHAR(Max)='';
Declare @select_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) + ']'
),
@select_cols = COALESCE (
@select_cols
+ ',COALESCE([' + CONVERT(NVARCHAR,CONVERT(VARCHAR(10), l_Date, 111), 106)
+ '], ' + CASE WHEN CONVERT(DATE, l_Date, 111) <= GETDATE()
THEN '''Absent'''
ELSE '''NULL'''
END + ') 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'
+ @select_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;
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