How to "duplicate" a row in SQL Server?

Question

I begin with SQL Server. I first wrote a query which creates a table. With this table I would like to add some rows.

The code below creates the table.

select 
    Element = [Key],
    New = max(case when time_index=1 then value end),
    'Current' = max(case when time_index>=2 then value end)
from
    (select 
         [time_index], B.*
     from   
         (select * 
          from ifrs17.output_bba 
          where id in (602677, 602777)) A
     cross apply 
         (select [Key], Value
          from OpenJson((select A.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) 
          where [Key] not in ('time_index')) B
    ) A
group by 
    [Key]

The result is here

Element	New	Current
AAA	10	20
BBB	15	34
CCC	17	22

Now, I would like to (for example) duplicate the second row ("BBB") and change the name ("Element") by "DDD".

Element	New	Current
AAA	10	20
BBB	15	34
CCC	17	22
DDD	15	34

Do you have an idea how to proceed?

Does this answer your question? [Possible to store value of one select column and use it for the next one?](https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one) You will find my answer there useful, see part 3 — Charlieface, Jan 21 '21 at 14:10
If you see the reference I showed you, you will see how to double up as much as you want. Within the `apply`, use whichever combination of `union all` and `where` to get the desired result — Charlieface, Jan 21 '21 at 14:26

score 0 · Accepted Answer · answered Jan 21 '21 at 14:09

0

not sure if I understand exactly but something like this maybe:

with X as 
(
select Element = [Key]
    ,New = max(case when time_index=1 then value end)
    ,'Current' = max(case when time_index>=2 then value end)
From  (
    Select [time_index]
            ,B.*
        From  (select * from ifrs17.output_bba where id in (602677,602777)) A
        Cross Apply (
                    Select [Key]
                        ,Value
                    From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                    Where [Key] not in ('time_index')
                    ) B
    ) A
Group By [Key]
)
select Element, New, [Current] from X 
union all
select 'DDD' as Element, New, [Current] from X where Element = 'BBB'

answered Jan 21 '21 at 14:09

Johnny Fitz

532
2
8

1

You can `cross apply` that `union all` and refer to the existing outer query rows, instead of doing another index scan/seek on `X` – Charlieface Jan 21 '21 at 14:12
thanks @Charlieface ! it works. What if i would like to add severals rows? for example, duplicate element BBB (as in the example) and duplicate also element CCC ? – Nassim Jan 21 '21 at 14:21
@Charlieface: does cross apply against X not result in two table scans anyway? How do you write that? – Johnny Fitz Jan 21 '21 at 14:57
No it doesn't if you use outer references instead of a `from`. I'll give an answer myself Edit: See my answer – Charlieface Jan 21 '21 at 17:31

score 0 · Answer 2 · answered Jan 21 '21 at 17:35

Quoting from Johnny Fitz's answer, you can do this without needing an extra table scan on X, by putting the UNION ALL inside a CROSS APPLY and referenncing the other parts pf the query:

with X as 
(
select Element = [Key]
    ,New = max(case when time_index=1 then value end)
    ,'Current' = max(case when time_index>=2 then value end)
From  (
    Select [time_index]
            ,B.*
        From  (select * from ifrs17.output_bba where id in (602677,602777)) A
        Cross Apply (
                    Select [Key]
                        ,Value
                    From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                    Where [Key] not in ('time_index')
                    ) B
    ) A
Group By [Key]
)

select
    v.Element,
    X.New,
    X.[Current]
from X 
cross apply (
    select x.Element
    union all
    select 'DDD' as Element
    where X.Element = 'BBB'
) v

The APPLY conditionally adds an extra row when it encounters BBB

score 0 · Answer 3 · answered Jan 11 '22 at 13:28

In general to duplicate (or multiplicate) rows I use Left JOIN (or INNER JOIN) to VALUES

SELECT * FROM MyTable   -- (id,value)
LEFT JOIN (VALUES (1),(2),(3) )AS t(k)  ON 
--what should be multiplicated and how
(id IN (1,2,3) and k<=2)
or id not in (1,2,3) and k<=3

Works with INNER join but then at least 1 row from K has to be joined

How to "duplicate" a row in SQL Server?

3 Answers3