How to "duplicate" a row in SQL Server?

user_13841755 0
Viewed 246

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?

union sql-server sql
3 Answers

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

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

user_148689970

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

user_45325850
Related