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

Question

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.

score 2 · Accepted Answer · answered Apr 27 '23 at 08:41

2

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';

answered Apr 27 '23 at 08:41

Thom A

88,727
11
45
75

A bit of a nitpick, you could've done VALUES(YT.HOST), ('Pete') – siggemannen Apr 27 '23 at 08:52
I was also tempted to parametrise, @siggemannen . There's a few different ways you can "skin the cat" here. – Thom A Apr 27 '23 at 08:54

score 0 · Answer 2 · answered Apr 27 '23 at 08:44

0

A simple INSERT INTO combined with a SELECT should do the trick.

INSERT INTO
  dbo.tablename (VALUE, PROPERTY, HOST)
SELECT
  VALUE, PROPERTY, 'Pete'
FROM
  dbo.tablename
WHERE
  HOST = 'Dave'

answered Apr 27 '23 at 08:44

Daniel Bürckner

362
1
10

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

2 Answers2