CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2008pivot
Mr doubt
Mr doubt

Reputation: 65

Assign default values in SQL Server dynamic pivot

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

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

Upvotes: 0

Views: 747

Answers (1)

GMB
GMB

Reputation: 222702

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;

Demo on DB Fiddle:

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

Upvotes: 1

Related Questions