locked
If thers is Same ids then one row should be appeared instead of 2rows 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 4:52 AM

All replies

  • Then your final query should look like this:

    SELECT  Emp_id, Rs_Id, Task_ID,
            SUM(CASE WHEN Date = '20200601' THEN Hours END) AS [20200601],
            SUM(CASE WHEN Date = '20200602' THEN Hours END) AS [20200602],
            ...
    FROM    #TMP_TABLE
    GROUP   BY  Emp_id, Rs_Id and Task_ID

    Stay away from the PIVOT keyword, it only makes things more difficult. The above partern is much more flexible.

    I have written more about dynamic pivot here:
    http://www.sommarskog.se/dynamic_sql.html#pivot

    Here you can learn to generate the above. You can also learn how to generate a concatenated string in a safe way; the model you use is not guaranteed to work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 10, 2020 8:06 AM
  • Hi Erland,

    thanks for your reply,

    But i want mothly dates daynamically.

    Can pplease suggest how to that query.

    thanks and regards,

    Priya.

    Monday, August 10, 2020 9:58 AM
  • thanks for your reply,

    But i want mothly dates daynamically.

    Can pplease suggest how to that query.

    I repeat from my previous post:

    ...............................................................

    I have written more about dynamic pivot here:
    http://www.sommarskog.se/dynamic_sql.html#pivot

    Here you can learn to generate the above. You can also learn how to generate a concatenated string in a safe way; the model you use is not guaranteed to work.

    ...............................................................

    You see, rather than spoon-feeding you a query, I like to learn how to do it, so that you have something you can use in the future next time the problem comes up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 10, 2020 9:46 PM