locked
Create duplicated rows of data on purpose RRS feed

  • Question

  • User-430413477 posted

    I am an beginner / intermediate sql user

    I have a database table called loadlists

    My client wants me to duplicate each row of data 2 times and display all rows and duplicated rows on a page.
    One field in each of the duplicated rows would be modified.

    Is there a way to do this in 1 sql query?

    I was going to create another table called loadlistsDuplicated.
    Then go thru each loadlists record and create 2 copies in loadlistsDuplicated table.
    Then on the web page show a joined view of all the rows

    The data on the web page is for display only.

    Loadlists data cannot be modified

    Friday, October 20, 2017 3:24 PM

Answers

  • User-430413477 posted

    This seems to work......

    I would also like PickupDate incremented by 1 business day in the second and third row

    October 20,2017, 1, Craig, Joans
    October 23,2017, 1, Craig, Joans
    October 24,2017, 1, Craig, Joans

    I may have to iterate through the rows in a separate table

    Seem like too much to try in one sql statement

    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 0, ScheduledDate) AS ScheduledDate
    FROM            Customers
    UNION ALL
    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 2, ScheduledDate) AS ScheduledDate
    FROM            Customers AS Customers_1
    UNION ALL
    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 3, ScheduledDate) AS ScheduledDate
    FROM            Customers AS Customers_1
    ORDER BY CustomerID

      203 NULL DELTA GAMMA SOR 10/20/2017 10/20/2017
      203 NULL DELTA GAMMA SOR 10/20/2017 10/23/2017
      203 NULL DELTA GAMMA SOR 10/20/2017 10/24/2017
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 20, 2017 10:08 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select *
    from loadlists
    cross apply
    (
        values (1), (2)
    ) as ca (seq)

    Hope this help

    Friday, October 20, 2017 4:58 PM
  • User77042963 posted

    You can create a view for your page.

    The view will have two selects from your table and union all together and you can modify data row in one set of select and it will not have impact on your base table.

    Create view duploadlistsView

    As

    select col1, col2 from loadlists

    UNION ALL

    select col1, col2='add somthing' as col2  from loadlists

    Friday, October 20, 2017 8:39 PM
  • User-430413477 posted

    This seems to work......

    I would also like PickupDate incremented by 1 business day in the second and third row

    October 20,2017, 1, Craig, Joans
    October 23,2017, 1, Craig, Joans
    October 24,2017, 1, Craig, Joans

    I may have to iterate through the rows in a separate table

    Seem like too much to try in one sql statement

    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 0, ScheduledDate) AS ScheduledDate
    FROM            Customers
    UNION ALL
    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 2, ScheduledDate) AS ScheduledDate
    FROM            Customers AS Customers_1
    UNION ALL
    SELECT        CustomerID, FirstName, LastName, ScheduledDate, DATEADD(d, 3, ScheduledDate) AS ScheduledDate
    FROM            Customers AS Customers_1
    ORDER BY CustomerID

      203 NULL DELTA GAMMA SOR 10/20/2017 10/20/2017
      203 NULL DELTA GAMMA SOR 10/20/2017 10/23/2017
      203 NULL DELTA GAMMA SOR 10/20/2017 10/24/2017
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 20, 2017 10:08 PM
  • User347430248 posted

    Hi craigbtx,

    from your last post , it looks like your issue is solved now.

    this thread is still open.

    if your issue is solved then I suggest you to mark the suggestion as an answer provided by the limno.

    until you mark the answer, this thread will remain open.

    if you have any further question then let us know about that.

    we will try to provide you further suggestions.

    Regards

    Deepak

    Monday, October 23, 2017 2:47 AM