locked
If same IDS are displayed diffrent rows then should be displayed single row RRS feed

  • Question

  •  Hi All,

    I am writting below pvote table query . I got result below table,in this i want modification in requirement.

    If there is same Emp_id, Rs_Id and Task_ID then one row should be appeared instead of 2 rows.

    Please help me in this query

    IF OBJECT_ID('TEMPDB..#TMP_TABLE') IS NOT NULL 

    DROP TABLE #TMP_TABLE 

     

    SELECT *  INTO #TMP_TABLE  FROM  [dbo].[DAILY_EFFORTS] 

     

    DECLARE 

    @COL NVARCHAR(MAX) 

     

    SELECT @COL = COALESCE (@COL + ',[' + CONVERT(NVARCHAR,[Date], 106) + ']', 

        '[' + CONVERT (NVARCHAR,[Date], 106) + ']') 

        FROM (SELECT DISTINCT [Date] FROM #TMP_TABLE) PV 

                    WHERE [DATE] BETWEEN @StartDate and @Enddate

        

        ORDER BY [Date] 

     

    DECLARE @query NVARCHAR(MAX) 

    SET @query = ' 

       SELECT * FROM 

       ( 

        SELECT * FROM #TMP_TABLE 

      ) x 

       PIVOT 

       ( 

        SUM(Hours) 

        for [Date] IN (' + @COL + ') 

        ) p' 

     

        EXEC SP_EXECUTESQL @query 

     

    SL_NO emp_id RS_ID Task_ID created date updated date Active 30-Dec-19 1-Jan-20 2-Jan-20 3-Jan-20 4-Jan-20
    1 2588 20 1 1/2/2020 1/2/2020 A NULL NULL 5 NULL NULL
    2 1331 18 4 1/2/2020 1/2/2020 A NULL NULL 2 NULL NULL
    3 2588 20 2 1/2/2020 NULL A NULL NULL 4 NULL NULL
    4 888 8 8 1/2/2020 NULL A NULL NULL 4 NULL NULL
    5 2160 8 4 1/2/2020 NULL A NULL NULL 4 NULL NULL
    6 888 8 11 1/2/2020 NULL A NULL NULL 1 NULL NULL
    7 2588 20 1 1/2/2020 NULL A NULL NULL NULL 4 NULL
    8 2588 20 9 1/2/2020 NULL A NULL NULL NULL 5 NULL
    9 1331 18 11 1/2/2020 1/2/2020 A NULL NULL 1 NULL NULL
    10 1331 18 4 1/2/2020 1/2/2020 A NULL NULL NULL 2 NULL
    11 1331 18 11 1/2/2020 1/2/2020 A NULL NULL NULL 1 NULL
    12 888 8 8 1/2/2020 NULL A NULL NULL NULL 4 NULL
    13 2160 8 4 1/2/2020 NULL A NULL NULL NULL 4 NULL
    14 2160 8 11 1/2/2020 NULL A NULL NULL NULL 1 NULL
    15 2291 18 1 1/2/2020 NULL A NULL NULL 5 NULL NULL
    16 2291 18 11 1/2/2020 NULL A NULL NULL 1 NULL NULL
    17 2291 18 1 1/2/2020 NULL A NULL NULL NULL 5 NULL
    18 2291 18 11 1/2/2020 NULL A NULL NULL NULL 1 NULL
    19 172 12 7 1/2/2020 NULL A NULL NULL 4 NULL NULL
    20 1844 19 4 1/2/2020 ######## A NULL NULL 4 NULL NULL
    Monday, August 10, 2020 5:12 AM

All replies