sql-serverconditional-statementssql-insertsql-server-2019-express

Create new row with a one new value based on value in another row

What I have:

VALUE PROPERTY HOST
One 1 Dave
Two 2 Dave
Three 3 Dave

What I want to achieve:

VALUE PROPERTY HOST
One 1 Dave
Two 2 Dave
Three 3 Dave
One 1 Pete
Two 2 Pete
Three 3 Pete

So I'm trying to create new rows based on any rows that have Dave as the host. Those rows are identical to the Dave rows except the Host is Pete. The Dave rows are retained. The Pete rows are added.

Solution

  • Just use a VALUES table construct with both Dave and John in the data:

    SELECT YT.Value,
       YT.Property,
       V.Host
FROM dbo.YourTable YT
     CROSS APPLY (VALUES('Dave'),
                        ('Pete'))V(Host)
WHERE YT.Host = 'Dave';