locked
How to repeat last row of table in sql server RRS feed

  • Question

  • This my employees table.

    Now when i want to retrieve the list of employees in department id 1 then I will get 5 records and the same goes for department id 2.

    But when I will be retrieving the list of employees in department id 3 then I will get 3 records. What should happen is that while retrieving the records for department id 3 , as there are only 3 records the last record should be replicated in the next two rows making a total of 5  records for department id 3. i.e the no of rows of rows while retrieving records for each department id should be same. If any list of records for particular department id is less than the maximum number of records for any department then the last record should be duplicated to make the number of rows for each search as same which in my case, the number of records for department id 3,4,5 should be list of 5 records. 

    • Moved by Olaf HelperMVP Tuesday, April 29, 2014 7:37 AM Moved from "SQL Database Engine" to a more related forum
    Tuesday, April 29, 2014 6:52 AM

Answers

  • Below is one method to accomplish the task.  I agree this doesn't make sense but we don't know anything about the business requirement that drives this request.  There may be better ways to accomplish this but we only see the query results here rather than the schema of the table(s) involved.

    WITH
    	years AS (
    		SELECT DISTINCT Year AS Year
    		FROM dbo.Foo
    		)
    	, departments AS (
    		SELECT DepartmentID, MAX(Year) AS MaxYear
    		FROM dbo.Foo
    		GROUP BY DepartmentID
    	)
    SELECT
    	 departments.DepartmentID
    	,COALESCE(Foo.EmpID, new_rows.EmpID) AS EmpID
    	,COALESCE(Foo.EmpName, new_rows.EmpName) AS EmpID
    	,years.Year
    FROM departments
    CROSS JOIN years
    LEFT JOIN dbo.Foo ON
    	Foo.DepartmentID = departments.DepartmentID
    	AND Foo.Year = years.Year
    JOIN dbo.Foo AS new_rows ON
    	new_rows.DepartmentID = departments.DepartmentID
    	AND new_rows.Year = departments.MaxYear;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman Toth Tuesday, April 29, 2014 12:21 PM
    • Marked as answer by Sumit.pareek Tuesday, April 29, 2014 12:29 PM
    Tuesday, April 29, 2014 12:01 PM

All replies

  • >as there are only 3 records the last record should be replicated in the next two rows making a total of 5  records for department id 3.

    That does not make sense. This is usually what we want to avoid in RDBMS: data duplication.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Tuesday, April 29, 2014 7:15 AM
  • This my employees table.

    Now when i want to retrieve the list of employees in department id 1 then I will get 5 records and the same goes for department id 2.

    But when I will be retrieving the list of employees in department id 3 then I will get 3 records. What should happen is that while retrieving the records for department id 3 , as there are only 3 records the last record should be replicated in the next two rows making a total of 5  records for department id 3. i.e the no of rows of rows while retrieving records for each department id should be same. If any list of records for particular department id is less than the maximum number of records for any department then the last record should be duplicated to make the number of rows for each search as same which in my case, the number of records for department id 3,4,5 should be list of 5 records. 


    Why on earth would you want to do this? As Kalman said, it doesn't make any sense. One of the first things you learn in database modelling is about primary and foreign key relationships and normal form. This will be a hindrance more than a help to you.
    Tuesday, April 29, 2014 8:15 AM
  • First of all Thanks for your reply.

     I know it does not make sense  Mr. Kalman, Johnny Bell but this is requirement from business. The year column is a counter.  As we insert data the year column also increases. You can say that it increments as the data is entered in particular department id. So when I am replicating the last row when the count is less, at that time the year column also increases accordingly like for department id 3 I have replicated the 3rd row two times and incremented the year column as 4 and 5. Same goes for department id 4 and id 5. Can it be Done?

      

    Tuesday, April 29, 2014 9:49 AM
  • This my employees table.

    Now when i want to retrieve the list of employees in department id 1 then I will get 5 records and the same goes for department id 2.

    But when I will be retrieving the list of employees in department id 3 then I will get 3 records. What should happen is that while retrieving the records for department id 3 , as there are only 3 records the last record should be replicated in the next two rows making a total of 5  records for department id 3. i.e the no of rows of rows while retrieving records for each department id should be same. The year column is a counter.  As we insert data the year column also increases. You can say that it increments as the data is entered in particular department id. So when I am replicating the last row when the count is less, at that time the year column also increases accordingly like for department id 3 I have replicated the 3rd row two times and incremented the year column as 4 and 5. Same goes for department id 4 and id 5. I want to retrieve  data like as below table .  Can it be Done?

    • Merged by Kalman Toth Tuesday, April 29, 2014 12:17 PM Duplicate
    Tuesday, April 29, 2014 11:55 AM
  • Below is one method to accomplish the task.  I agree this doesn't make sense but we don't know anything about the business requirement that drives this request.  There may be better ways to accomplish this but we only see the query results here rather than the schema of the table(s) involved.

    WITH
    	years AS (
    		SELECT DISTINCT Year AS Year
    		FROM dbo.Foo
    		)
    	, departments AS (
    		SELECT DepartmentID, MAX(Year) AS MaxYear
    		FROM dbo.Foo
    		GROUP BY DepartmentID
    	)
    SELECT
    	 departments.DepartmentID
    	,COALESCE(Foo.EmpID, new_rows.EmpID) AS EmpID
    	,COALESCE(Foo.EmpName, new_rows.EmpName) AS EmpID
    	,years.Year
    FROM departments
    CROSS JOIN years
    LEFT JOIN dbo.Foo ON
    	Foo.DepartmentID = departments.DepartmentID
    	AND Foo.Year = years.Year
    JOIN dbo.Foo AS new_rows ON
    	new_rows.DepartmentID = departments.DepartmentID
    	AND new_rows.Year = departments.MaxYear;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Kalman Toth Tuesday, April 29, 2014 12:21 PM
    • Marked as answer by Sumit.pareek Tuesday, April 29, 2014 12:29 PM
    Tuesday, April 29, 2014 12:01 PM
  • This is a duplicate question. 

    Moderator, please merge with http://social.msdn.microsoft.com/Forums/en-US/c0172cfb-de8d-4ba8-b43a-8dcdb176f95c/how-to-repeat-last-row-of-table-in-sql-server?forum=transactsql so that we don't have community contributors performing duplication effort to answer the same question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, April 29, 2014 12:05 PM
  • Thanks Dan. This is working for me 
    Tuesday, April 29, 2014 12:32 PM